User Form VBA - Skip a text box from editing
Results 1 to 2 of 2

Thread: User Form VBA - Skip a text box from editing
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default User Form VBA - Skip a text box from editing

    Hello,

    I have a user form that has 96 text boxes labeled R1 through R96.
    I have it so once the data is filled in you hit submit and it posts everything from R1 through R94. That part works great.

    The reason I dont post R95 or R96 is because I have formulas in the cells.

    I can then open the userform and search the data and import it all back in to form and edit as needed. It also pulls in the formula information to R95 and R96. Works Great.

    The problem I am having is when I import it back in then use the below below VBA to edit it - it overwrites the R95 and R96 deleting the formulas. I cannot figure out how to have skip over them.


    Private Sub cmdEdit_Click()
    'declare the variables
    Dim findvalue As Range
    'error handling
    On Error GoTo errHandler:
    'check for values
    If R1.Value = "" Or R2.Value = "" Then
    MsgBox "There is not data to edit"
    Exit Sub
    End If
    'edit the row
    Set findvalue = Sheet2.Range("F:F").Find(What:=R4, LookIn:=xlValues).Offset(0, -3)
    'if the edit is a name then add it

    For x = 1 To cNum
    findvalue = Me.Controls("R" & x).Value
    Set findvalue = findvalue.Offset(0, 1)
    Next
    'refresh the listbox
    Lookup
    'error block
    On Error GoTo 0
    Exit Sub
    errHandler:
    MsgBox "An Error has Occurred " & vbCrLf & _
    "The error number is: " & Err.Number & vbCrLf & _
    Err.Description & vbCrLf & "Please notify the administrator"
    End Sub

  2. #2
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User Form VBA - Skip a text box from editing

    I am an idiot - I just realized I can add! Solved my own dang problem

    'number of controls to loop through cNum = 94

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •