none
Searching entire Worksheet with userform to find and edit if value is found RRS feed

  • Question

  • I have created a VBA to search Column A:A for a certain number, if found, it populates text and combo boxes in the form. I have this successful to work in just one of four sheets. How can I use the same userform, and have it search all four sheets to do the same thing? Due to company policy I cannot upload a copy of the file, but would like some help getting this to work. The four sheets are named "Progress, Ready,Active, Closed & Completed".

    This is for my Command button to Find the value and populate the boxes..

    Dim r As Excel.Range
    Set r = Sheets("Progress").Range("A:A").Find(What:=TextBox1.Text, lookAt:=xlWhole, MatchCase:=False)
    If r Is Nothing Then
    MsgBox "# not found", vbExclamation
    TextBox1.Text = ""

    Else
    TextBox2.Text = Sheets("Progress").Range("B" & r.Row)
    TextBox3.Text = Sheets("Progress").Range("C" & r.Row)
    TextBox4.Text = Sheets("Progress").Range("D" & r.Row)
    ComboBox1.Text = Sheets("Progress").Range("E" & r.Row)
    ComboBox2.Text = Sheets("Progress").Range("G" & r.Row)
    ComboBox3.Text = Sheets("Progress").Range("H" & r.Row)
    ComboBox4.Text = Sheets("Progress").Range("I" & r.Row)
    TextBox5.Text = Sheets("Progress").Range("J" & r.Row)
    TextBox6.Text = Sheets("Progress").Range("N" & r.Row)
    r.Select

    Thank you all very much for your help in advance!

    Tuesday, December 12, 2017 8:33 PM

Answers

  • Hello dross85,

    >>this only searches the first worksheet in the split

    It seems that there is a space character at the start of the worksheet. You could try to use Trim function to remove it. For example,

    For Each s In Split("Progress, Ready, Active, Closed&Completed", ",")
        nSet Trim(s), TextBox1
      Next s

    >>this msgbox comes up even though it will find the Value in Column A of the first sheet

    This msgbox comes up once it failed to find the text in any worksheet. You could add sheet name in the msgbox message so it may be more clearly. For example.

    MsgBox "# not found in Sheet:" & ws, vbExclamation

    If you only want to show message while failing to find the text in all worksheets. You could use a global boolean variable, once you find the text in any sheet, mark the boolean value as true. At last, you could know if the macro fail to find the text in all worksheets according to the boolean value.

    For example.

    Dim flag As Boolean
    Private Sub CommandButton1_Click()
    Dim s
        flag = False
      For Each s In Split("Progress, Ready, Active, Closed&Completed", ",")
        nSet Trim(s), TextBox1
      Next s
        If flag = False Then
        MsgBox TextBox1 & " not found", vbExclamation
        End If
    End Sub
    
    
    Private Sub nSet(ws, tb As Control)
      Dim r As Excel.Range
      On Error Resume Next
      If Sheets(ws) Is Nothing Then
      MsgBox "Sheet:" & ws & "does not exist"
      Exit Sub
      End If
      
      Set r = Sheets(ws).Range("A:A").Find(What:=tb.Text, lookAt:=xlWhole, MatchCase:=False)
      If r Is Nothing Then
      'this msgbox comes up even though it will find the Value in Column A of the first sheet
        'MsgBox "# not found in Sheet:" & ws, vbExclamation
        Else
          flag = True
          TextBox2.Text = Sheets(ws).Range("B" & r.Row)
          TextBox3.Text = Sheets(ws).Range("C" & r.Row)
          TextBox4.Text = Sheets(ws).Range("D" & r.Row)
          ComboBox1.Text = Sheets(ws).Range("E" & r.Row)
          ComboBox2.Text = Sheets(ws).Range("G" & r.Row)
          ComboBox3.Text = Sheets(ws).Range("H" & r.Row)
          ComboBox4.Text = Sheets(ws).Range("I" & r.Row)
          TextBox5.Text = Sheets(ws).Range("J" & r.Row)
          TextBox6.Text = Sheets(ws).Range("N" & r.Row)
          r.Select
      End If
    ActiveCell.Resize(1, 14).Select
    TextBox1.SetFocus
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by dross85 Thursday, December 14, 2017 11:03 PM
    Thursday, December 14, 2017 6:11 AM

All replies

  • Untestted:

    Private Sub CommandButton1_Click()
      Dim s
      For Each s In Split("Progress Ready,Active,Closed,Completed", ",")
        nSet s, TextBox1
      Next s
    End Sub
    
    Private Sub nSet(ws, tb As Control)
      Dim r As Excel.Range
      Set r = Sheets(ws).Range("A:A").Find(What:=tb.Text, lookAt:=xlWhole, MatchCase:=False)
      If r Is Nothing Then
        MsgBox "# not found", vbExclamation
        tb.Text = ""
        
        Else
          TextBox2.Text = Sheets(ws).Range("B" & r.Row)
          TextBox3.Text = Sheets(ws).Range("C" & r.Row)
          TextBox4.Text = Sheets(ws).Range("D" & r.Row)
          ComboBox1.Text = Sheets(ws).Range("E" & r.Row)
          ComboBox2.Text = Sheets(ws).Range("G" & r.Row)
          ComboBox3.Text = Sheets(ws).Range("H" & r.Row)
          ComboBox4.Text = Sheets(ws).Range("I" & r.Row)
          TextBox5.Text = Sheets(ws).Range("J" & r.Row)
          TextBox6.Text = Sheets(ws).Range("N" & r.Row)
          r.Select
      End If
    End Sub

    • Proposed as answer by Terry Xu - MSFT Wednesday, December 13, 2017 9:58 AM
    Tuesday, December 12, 2017 9:05 PM
  • Thanks for the reply, I am getting an Subscript out of range for:

    Set r = Sheets(ws).Range("A:A").Find(What:=tb.Text, lookAt:=xlWhole, MatchCase:=False)

    Tuesday, December 12, 2017 9:30 PM
  • Hello dross85,

    I think Kenneth missed a "," in the worksheet name string, so the nSet will take "Progress Ready" as the sheet name to get the sheet range. Obviously you do not have the sheet and then it will cause the error.

    You could update the Sheet name string according to your need.Such as 

    Private Sub CommandButton1_Click()
      Dim s
      For Each s In Split("Progress,Ready,Active,Closed,Completed", ",")
        nSet s, TextBox1
      Next s
    End Sub

    Besides, I would suggest you check if the worksheet exist before setting the range variable.

    Private Sub nSet(ws, tb As Control)
      Dim r As Excel.Range
      On Error Resume Next
      If Sheets(ws) Is Nothing Then
      MsgBox "Sheet:" & ws & "does not exist"
      Exit Sub
      End If
      Set r = Sheets(ws).Range("A:A").Find(What:=tb.Text, lookAt:=xlWhole, MatchCase:=False)
      If r Is Nothing Then
        MsgBox "# not found", vbExclamation
        tb.Text = ""
        
        Else
          TextBox2.Text = Sheets(ws).Range("B" & r.Row)
          TextBox3.Text = Sheets(ws).Range("C" & r.Row)
          TextBox4.Text = Sheets(ws).Range("D" & r.Row)
          ComboBox1.Text = Sheets(ws).Range("E" & r.Row)
          ComboBox2.Text = Sheets(ws).Range("G" & r.Row)
          ComboBox3.Text = Sheets(ws).Range("H" & r.Row)
          ComboBox4.Text = Sheets(ws).Range("I" & r.Row)
          TextBox5.Text = Sheets(ws).Range("J" & r.Row)
          TextBox6.Text = Sheets(ws).Range("N" & r.Row)
          r.Select
      End If
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Terry Xu - MSFT Wednesday, December 13, 2017 9:58 AM
    Wednesday, December 13, 2017 5:55 AM
  • Terry-

    We are very close, the codes do not give an error in my Worksheet, but they do not find a result on any sheet except "Progress". Each input into my TextBox1 comes back with MsgBox "# not found". I will keep trying to modify it, but please let me know if you see a fix to the problem, I will mark as answered. Looking forward to having this 100%!

    This is where I'm at, with no debug errors:

    Private Sub CommandButton1_Click()
      'this only searches the first worksheet in the split
      Dim s
      For Each s In Split("Progress, Ready, Active, Closed & Completed", ",")
        nSet s, TextBox1
      Next s
    End Sub

    Private Sub nSet(ws, tb As Control)
      Dim r As Excel.Range
      On Error Resume Next
      If Sheets(ws) Is Nothing Then
      MsgBox "Sheet:" & ws & "does not exist"
      
      Exit Sub
      End If
     
      Set r = Sheets(ws).Range("A:A").Find(What:=tb.Text, lookAt:=xlWhole, MatchCase:=False)
      If r Is Nothing Then
      'this msgbox comes up even though it will find the Value in Column A of the first sheet
        MsgBox "# not found", vbExclamation
        tb.Text = ""
       
        Else
          TextBox2.Text = Sheets(ws).Range("B" & r.Row)
          TextBox3.Text = Sheets(ws).Range("C" & r.Row)
          TextBox4.Text = Sheets(ws).Range("D" & r.Row)
          ComboBox1.Text = Sheets(ws).Range("E" & r.Row)
          ComboBox2.Text = Sheets(ws).Range("G" & r.Row)
          ComboBox3.Text = Sheets(ws).Range("H" & r.Row)
          ComboBox4.Text = Sheets(ws).Range("I" & r.Row)
          TextBox5.Text = Sheets(ws).Range("J" & r.Row)
          TextBox6.Text = Sheets(ws).Range("N" & r.Row)
          r.Select
      End If
    ActiveCell.Resize(1, 14).Select


    TextBox1.SetFocus
    End Sub



    • Edited by dross85 Wednesday, December 13, 2017 6:57 PM
    Wednesday, December 13, 2017 4:35 PM
  • Hello dross85,

    >>this only searches the first worksheet in the split

    It seems that there is a space character at the start of the worksheet. You could try to use Trim function to remove it. For example,

    For Each s In Split("Progress, Ready, Active, Closed&Completed", ",")
        nSet Trim(s), TextBox1
      Next s

    >>this msgbox comes up even though it will find the Value in Column A of the first sheet

    This msgbox comes up once it failed to find the text in any worksheet. You could add sheet name in the msgbox message so it may be more clearly. For example.

    MsgBox "# not found in Sheet:" & ws, vbExclamation

    If you only want to show message while failing to find the text in all worksheets. You could use a global boolean variable, once you find the text in any sheet, mark the boolean value as true. At last, you could know if the macro fail to find the text in all worksheets according to the boolean value.

    For example.

    Dim flag As Boolean
    Private Sub CommandButton1_Click()
    Dim s
        flag = False
      For Each s In Split("Progress, Ready, Active, Closed&Completed", ",")
        nSet Trim(s), TextBox1
      Next s
        If flag = False Then
        MsgBox TextBox1 & " not found", vbExclamation
        End If
    End Sub
    
    
    Private Sub nSet(ws, tb As Control)
      Dim r As Excel.Range
      On Error Resume Next
      If Sheets(ws) Is Nothing Then
      MsgBox "Sheet:" & ws & "does not exist"
      Exit Sub
      End If
      
      Set r = Sheets(ws).Range("A:A").Find(What:=tb.Text, lookAt:=xlWhole, MatchCase:=False)
      If r Is Nothing Then
      'this msgbox comes up even though it will find the Value in Column A of the first sheet
        'MsgBox "# not found in Sheet:" & ws, vbExclamation
        Else
          flag = True
          TextBox2.Text = Sheets(ws).Range("B" & r.Row)
          TextBox3.Text = Sheets(ws).Range("C" & r.Row)
          TextBox4.Text = Sheets(ws).Range("D" & r.Row)
          ComboBox1.Text = Sheets(ws).Range("E" & r.Row)
          ComboBox2.Text = Sheets(ws).Range("G" & r.Row)
          ComboBox3.Text = Sheets(ws).Range("H" & r.Row)
          ComboBox4.Text = Sheets(ws).Range("I" & r.Row)
          TextBox5.Text = Sheets(ws).Range("J" & r.Row)
          TextBox6.Text = Sheets(ws).Range("N" & r.Row)
          r.Select
      End If
    ActiveCell.Resize(1, 14).Select
    TextBox1.SetFocus
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by dross85 Thursday, December 14, 2017 11:03 PM
    Thursday, December 14, 2017 6:11 AM
  • Terry, this is working Beautifully!

    Thank you so much. Which part of the code do I write my ws.select to call the sheet that has my number?

    I have the r.select - this lets the data be selected if I have that sheet active when I click the command button.

    How would I make the workbook activate the actual sheet where the data is found without having to click through each sheet tab and click the command button again to select the data?

    With one click of the command button, I would like it to activate the sheet of the found text, and select the data.

    Thanks

    EDIT. I added

    sheets(ws).activate

    r.select

    • Edited by dross85 Thursday, December 14, 2017 11:03 PM
    Thursday, December 14, 2017 5:34 PM