UserForm Search Function

C

cmerrick

Board Regular
Joined
Jun 8, 2017
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Good morning all,

I have a code that I am using to enable a UserForm to display info about employees. The user is then able to amend the data and the spreadsheet updates. I've got a search function working but I want the user to be able to search with one option OR the other option not a combination of the two.

My code is below.

Help is much appreciated.

Code: 
Private Sub CommandButton3_Click()
row_number = 0
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("All").Range("A" & row_number)
    If item_in_review = NetworkuserSearch.Text Then
        Sheets("All").Range("C" & row_number) = EmployeeIdSearch.Text
        Sheets("All").Range("D" & row_number) = FullnameSearch.Text
        Sheets("All").Range("B" & row_number) = Initials.Text
        Sheets("All").Range("E" & row_number) = Appteam.Text
        Sheets("All").Range("F" & row_number) = Deptcode.Text
        Sheets("All").Range("G" & row_number) = teamcode.Text
        Sheets("All").Range("H" & row_number) = Jobtitle.Text
        Sheets("All").Range("I" & row_number) = emailaddress.Text
        Sheets("All").Range("J" & row_number) = telephoneext.Text
        Sheets("All").Range("K" & row_number) = faxext.Text
        Sheets("All").Range("L" & row_number) = eventassign.Text
        Sheets("All").Range("M" & row_number) = signature.Text
        
     End If
Loop Until item_in_review = ""
        
If MsgBox("Are you sure you wish the submit these changes?", vbQuestion + vbYesNo) <> vbNo Then
     
Unload Me
Else
End If
        
End Sub
 
Sort by date Sort by votes
check the control names - I assumed you have 7 optionbuttons? named "yes1" "yes2" etc.

Dave
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I have 6 option buttons with Yes or a No option that then populates a Y or N in the data sheet and one optionbutton with a O or a U than then populates an O or U on the data sheet.

I'm assuming I need to repeat this line of code for each of the control names being yes1, yes2 etc...

Code: 
Me.Controls("yes1" & i).Value = CBool(.Text = "Y" Or .Text = "O")
Me.Controls("yes2" & i).Value = CBool(.Text = "Y" Or .Text = "O")
Me.Controls("yes1" & i).Value = CBool(.Text = "Y" Or .Text = "O")
etc...

regards

 
Upvote 0
cmerrick said:
I have 6 option buttons with Yes or a No option that then populates a Y or N in the data sheet and one optionbutton with a O or a U than then populates an O or U on the data sheet.

I'm assuming I need to repeat this line of code for each of the control names being yes1, yes2 etc...

Code: 
Me.Controls("yes1" & i).Value = CBool(.Text = "Y" Or .Text = "O")
Me.Controls("yes2" & i).Value = CBool(.Text = "Y" Or .Text = "O")
Me.Controls("yes1" & i).Value = CBool(.Text = "Y" Or .Text = "O")
etc...

regards

Click to expand...

you assume incorrectly - the for loop deals with all the optionbutton controls

try this update

Code: 
For i = 1 To 7
        With Sheets("All").Cells(row_number, 13 + i)
        If i = 6 Then
            Me.O6.Value = CBool(.Text = "O")
        Else
            Me.Controls("yes" & i).Value = CBool(.Text = "Y")
        End If
        End With
    Next i

Dave
 
Upvote 0
Hi Dave,

Thanks for the update date. I've just gotten around to testing it and unfortunately the optionbuttons do not populate on the UserForm as expected.

Regards
 
Upvote 0
cmerrick said:
Hi Dave,

Thanks for the update date. I've just gotten around to testing it and unfortunately the optionbuttons do not populate on the UserForm as expected.

Regards
Click to expand...

Hi,
publish code you are using which includes the update.

Dave
 
Upvote 0
Hi, thank you very much for you help on this issue.

I've thrown together, what must be some very crude in comparison to what people on here are used to, since we last spoke but it does the job.

This is what I'm currently using to recall the information back into the user form;

Code: 
Private Sub CommandButton1_Click()
row_number = 2
Do
 DoEvents
  row_number = row_number + 1
  
item_in_review = Sheets("ALL").Range("D" & row_number)
    If item_in_review = FullnameSearch.Text Then
        NetworkuserSearch = Sheets("ALL").Range("A" & row_number)
        Initials = Sheets("ALL").Range("B" & row_number)
        EmployeeIdSearch = Sheets("ALL").Range("C" & row_number)
        Appteam = Sheets("ALL").Range("E" & row_number)
        Deptcode = Sheets("ALL").Range("F" & row_number)
        teamcode = Sheets("ALL").Range("G" & row_number)
        Jobtitle = Sheets("ALL").Range("H" & row_number)
        emailaddress = Sheets("ALL").Range("I" & row_number)
        telephoneext = Sheets("ALL").Range("J" & row_number)
        faxext = Sheets("ALL").Range("K" & row_number)
        eventassign = Sheets("ALL").Range("L" & row_number)
        signature = Sheets("ALL").Range("M" & row_number)
        
        
        
    If Sheets("ALL").Range("N" & row_number).Value = "Y" Then yes1.Value = True
    If Sheets("ALL").Range("N" & row_number).Value = "N" Then no1.Value = True
    
    
    If Sheets("ALL").Range("O" & row_number).Value = "Y" Then yes2.Value = True
    If Sheets("ALL").Range("O" & row_number).Value = "N" Then no2.Value = True
    
    
    If Sheets("ALL").Range("P" & row_number).Value = "Y" Then yes3.Value = True
    If Sheets("ALL").Range("P" & row_number).Value = "N" Then no3.Value = True
    
    
    If Sheets("ALL").Range("Q" & row_number).Value = "Y" Then yes4.Value = True
    If Sheets("ALL").Range("Q" & row_number).Value = "N" Then no4.Value = True
    
    
    If Sheets("ALL").Range("R" & row_number).Value = "Y" Then yes5.Value = True
    If Sheets("ALL").Range("R" & row_number).Value = "N" Then no5.Value = True
    
    
    If Sheets("ALL").Range("S" & row_number).Value = "O" Then O6.Value = True
    If Sheets("ALL").Range("S" & row_number).Value = "U" Then U6.Value = True
    
    
    If Sheets("ALL").Range("T" & row_number).Value = "Y" Then yes7.Value = True
    If Sheets("ALL").Range("T" & row_number).Value = "N" Then no7.Value = True
    
    End If
    
Loop Until item_in_review = ""
End Sub

and this is what I'm using to send the amended information back into the data sheet;

Code: 
 Private Sub CommandButton3_Click()
row_number = 2
Do
 DoEvents
  row_number = row_number + 1
  
item_in_review = Sheets("ALL").Range("D" & row_number)
    If item_in_review = FullnameSearch.Text Then
        Sheets("All").Range("A" & row_number) = NetworkuserSearch
        Sheets("All").Range("B" & row_number) = Initials.Text
        Sheets("All").Range("C" & row_number) = EmployeeIdSearch
        Sheets("All").Range("E" & row_number) = Appteam.Text
        Sheets("All").Range("F" & row_number) = Deptcode.Text
        Sheets("All").Range("G" & row_number) = teamcode.Text
        Sheets("All").Range("H" & row_number) = Jobtitle.Text
        Sheets("All").Range("I" & row_number) = emailaddress.Text
        Sheets("All").Range("J" & row_number) = telephoneext.Text
        Sheets("All").Range("K" & row_number) = faxext.Text
        Sheets("All").Range("L" & row_number) = eventassign.Text
        Sheets("All").Range("M" & row_number) = signature.Text
        
    If yes1.Value = True Then Sheets("All").Range("N" & row_number).Value = "Y"
        If no1.Value = True Then Sheets("All").Range("N" & row_number).Value = "N"
    
    If yes2.Value = True Then Sheets("All").Range("O" & row_number).Value = "Y"
        If no2.Value = True Then Sheets("All").Range("O" & row_number).Value = "N"
        
    If yes3.Value = True Then Sheets("All").Range("P" & row_number).Value = "Y"
        If no3.Value = True Then Sheets("All").Range("P" & row_number).Value = "N"
        
    If yes4.Value = True Then Sheets("All").Range("Q" & row_number).Value = "Y"
        If no4.Value = True Then Sheets("All").Range("Q" & row_number).Value = "N"
        
    If yes5.Value = True Then Sheets("All").Range("R" & row_number).Value = "Y"
        If no5.Value = True Then Sheets("All").Range("R" & row_number).Value = "N"
        
    If O6.Value = True Then Sheets("All").Range("S" & row_number).Value = "Y"
        If U6.Value = True Then Sheets("All").Range("S" & row_number).Value = "N"
        
    If yes7.Value = True Then Sheets("All").Range("T" & row_number).Value = "Y"
        If no7.Value = True Then Sheets("All").Range("T" & row_number).Value = "N"
        
    End If
        
Loop Until item_in_review = ""
        
If MsgBox("Are you sure you wish the submit these changes?", vbQuestion + vbYesNo) <> vbNo Then
     
Unload Me
 Else
  End If
        
End Sub


Seems to do everything I need including populating the optionbuttons which is what I have been struggling with.
 
Upvote 0
Optionbuttons only allow you to make one selection in each group - can you just confirm that these are OptionButtons you have on your form or are they Checkboxes?

Dave
 
Upvote 0
are you able to place copy of your workbook in dropbox?
 
Upvote 0
I'm sorry, the document is in my work computer and we don't have access to dropbox here
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
How to fix formats for the Listbox view in a userform?
Replies
5
Views
1K
Mayozero
M
E
  • Question
Search function not finding rows containing the matching value
Replies
1
Views
546
GlennUK
G
D
  • Question
User form to Search for and Update Data
Replies
4
Views
9K
Yrock
Y
D
  • Question
Userform Tab to produce vlookup
Replies
2
Views
541
drc2265
D
L
  • Question
Userform ComboBox Search WS and Populate Userform
Replies
0
Views
396
LavendarRabbit
L

Forum statistics

Threads
1,216,153
Messages
6,129,176
Members
449,491
Latest member
maxim_sivakon

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top