Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to repeat find on button click
Newbie and need help - assume zero knowledge! JLatham helped me with
this code, but don't know how to repeat it's function at CommandButton3. Have read the VBA help and still none the wiser! Would be very grateful if one of you guys would wave your magic wands! Here's the code: A BIG THANKYOU IN ADVANCE Jeanette Private Sub CommandButton1_Click() 'the "search" button will look for last name entries first 'if match is found, stops completely 'if no match found, checks to see if there's an entry in first name box and if there is, will search for match CommandButton1.Visible = True Const whatColor = 3 ' 3=red Dim searchList As Range Dim anyEntry As Range Dim findEntry As String 'get the surname entry 'remove leading/trailing whitespace and convert to UPPERCASE for tests findEntry = UCase(Trim(Me.TextBox1)) If findEntry < "" Then 'we do have a last name to seek 'look in column B for it Set searchList = ActiveSheet.Range("B1:" & _ ActiveSheet.Range("B" & Rows.Count).End(xlUp). _ Offset(1, 0).Address) For Each anyEntry In searchList If UCase(Trim(anyEntry)) = findEntry Then 'name matched, check formatting If anyEntry.Font.ColorIndex < whatColor And _ anyEntry.Font.Strikethrough = False Then anyEntry.Activate Set searchList = Nothing CommandButton1.Visible = False CommandButton3.Visible = True Exit Sub ' all finished End If End If Next 'if we get here, no match on lastname was found, see if we have a first name to look for findEntry = UCase(Trim(Me.TextBox1)) If findEntry < "" Then 'have a first name to look for 'look in column C for it Set searchList = ActiveSheet.Range("C1:" & _ ActiveSheet.Range("C" & Rows.Count).End(xlUp). _ Offset(1, 0).Address) For Each anyEntry In searchList If UCase(Trim(anyEntry)) = findEntry Then 'name matched, check formatting If anyEntry.Font.ColorIndex < whatColor And _ anyEntry.Font.Strikethrough = False Then anyEntry.Activate Set searchList = Nothing CommandButton1.Visible = False CommandButton3.Visible = True Exit Sub ' all finished End If End If Next End If Else 'did not have last name, do we have 'a first name to search for findEntry = UCase(Trim(Me.TextBox2)) If findEntry < "" Then 'have a first name to look for 'look in column C for it Set searchList = ActiveSheet.Range("C1:" & _ ActiveSheet.Range("C" & Rows.Count).End(xlUp). _ Offset(1, 0).Address) For Each anyEntry In searchList If UCase(Trim(anyEntry)) = findEntry Then 'name matched, check formatting If anyEntry.Font.ColorIndex < whatColor And _ anyEntry.Font.Strikethrough = False Then anyEntry.Activate Set searchList = Nothing CommandButton1.Visible = False CommandButton3.Visible = True Exit Sub ' all finished End If End If Next End If End If End Sub Private Sub CommandButton2_Click() 'the "cancel" button Unload Me End Sub Private Sub CommandButton3_Click() 'Do exactly the same again, but start it from the active cell End Sub Private Sub UserForm_Initialize() Me.TextBox1 = "" ' last name box Me.TextBox2 = "" ' first name box End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to repeat find on button click
What do you mean repeat its function?, can you explain a little better as a simple click of button 1 would run it again. Jeanette;403671 Wrote: Newbie and need help - assume zero knowledge! JLatham helped me with this code, but don't know how to repeat it's function at CommandButton3. Have read the VBA help and still none the wiser! Would be very grateful if one of you guys would wave your magic wands! Here's the code: A BIG THANKYOU IN ADVANCE Jeanette Code: -------------------- Private Sub CommandButton1_Click() 'the "search" button will look for last name entries first 'if match is found, stops completely 'if no match found, checks to see if there's an entry in first name box and if there is, will search for match CommandButton1.Visible = True Const whatColor = 3 ' 3=red Dim searchList As Range Dim anyEntry As Range Dim findEntry As String 'get the surname entry 'remove leading/trailing whitespace and convert to UPPERCASE for tests findEntry = UCase(Trim(Me.TextBox1)) If findEntry < "" Then 'we do have a last name to seek 'look in column B for it Set searchList = ActiveSheet.Range("B1:" & _ ActiveSheet.Range("B" & Rows.Count).End(xlUp). _ Offset(1, 0).Address) For Each anyEntry In searchList If UCase(Trim(anyEntry)) = findEntry Then 'name matched, check formatting If anyEntry.Font.ColorIndex < whatColor And _ anyEntry.Font.Strikethrough = False Then anyEntry.Activate Set searchList = Nothing CommandButton1.Visible = False CommandButton3.Visible = True Exit Sub ' all finished End If End If Next 'if we get here, no match on lastname was found, see if we have a first name to look for findEntry = UCase(Trim(Me.TextBox1)) If findEntry < "" Then 'have a first name to look for 'look in column C for it Set searchList = ActiveSheet.Range("C1:" & _ ActiveSheet.Range("C" & Rows.Count).End(xlUp). _ Offset(1, 0).Address) For Each anyEntry In searchList If UCase(Trim(anyEntry)) = findEntry Then 'name matched, check formatting If anyEntry.Font.ColorIndex < whatColor And _ anyEntry.Font.Strikethrough = False Then anyEntry.Activate Set searchList = Nothing CommandButton1.Visible = False CommandButton3.Visible = True Exit Sub ' all finished End If End If Next End If Else 'did not have last name, do we have 'a first name to search for findEntry = UCase(Trim(Me.TextBox2)) If findEntry < "" Then 'have a first name to look for 'look in column C for it Set searchList = ActiveSheet.Range("C1:" & _ ActiveSheet.Range("C" & Rows.Count).End(xlUp). _ Offset(1, 0).Address) For Each anyEntry In searchList If UCase(Trim(anyEntry)) = findEntry Then 'name matched, check formatting If anyEntry.Font.ColorIndex < whatColor And _ anyEntry.Font.Strikethrough = False Then anyEntry.Activate Set searchList = Nothing CommandButton1.Visible = False CommandButton3.Visible = True Exit Sub ' all finished End If End If Next End If End If End Sub Private Sub CommandButton2_Click() 'the "cancel" button Unload Me End Sub Private Sub CommandButton3_Click() 'Do exactly the same again, but start it from the active cell End Sub Private Sub UserForm_Initialize() Me.TextBox1 = "" ' last name box Me.TextBox2 = "" ' first name box End Sub -------------------- -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112570 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to repeat find on button click
when you find that you have similar code blocks - in this case two sets for finding data, then its more efficient to create one find process and call it twice Next Find the worksheet find looks at all cells for the text looked for, and when you get to the 'last' cell, the find algorithm returns to the first cell found and loops around again. so in code, when we find the first cell, we note its address, then find the next repeatedly until we get the address of the first cell again. This is well explained in Help, plus there's a code sample dim found as Range dim source as Range Set source = range("mydata") ' find first cell set found = source.Find("what??") 'check something found If Not found is nothing then 'something found 'so get its address addr = found.address 'start a loop to handle finds DO ' do something with the found cell 'find the next SET found = source.findNext(found) LOOP while found.address<addr Else msgbox "NO cells found" End if End Sub "Jeanette" wrote in message ... Newbie and need help - assume zero knowledge! JLatham helped me with this code, but don't know how to repeat it's function at CommandButton3. Have read the VBA help and still none the wiser! Would be very grateful if one of you guys would wave your magic wands! Here's the code: A BIG THANKYOU IN ADVANCE Jeanette Private Sub CommandButton1_Click() 'the "search" button will look for last name entries first 'if match is found, stops completely 'if no match found, checks to see if there's an entry in first name box and if there is, will search for match CommandButton1.Visible = True Const whatColor = 3 ' 3=red Dim searchList As Range Dim anyEntry As Range Dim findEntry As String 'get the surname entry 'remove leading/trailing whitespace and convert to UPPERCASE for tests findEntry = UCase(Trim(Me.TextBox1)) If findEntry < "" Then 'we do have a last name to seek 'look in column B for it Set searchList = ActiveSheet.Range("B1:" & _ ActiveSheet.Range("B" & Rows.Count).End(xlUp). _ Offset(1, 0).Address) For Each anyEntry In searchList If UCase(Trim(anyEntry)) = findEntry Then 'name matched, check formatting If anyEntry.Font.ColorIndex < whatColor And _ anyEntry.Font.Strikethrough = False Then anyEntry.Activate Set searchList = Nothing CommandButton1.Visible = False CommandButton3.Visible = True Exit Sub ' all finished End If End If Next 'if we get here, no match on lastname was found, see if we have a first name to look for findEntry = UCase(Trim(Me.TextBox1)) If findEntry < "" Then 'have a first name to look for 'look in column C for it Set searchList = ActiveSheet.Range("C1:" & _ ActiveSheet.Range("C" & Rows.Count).End(xlUp). _ Offset(1, 0).Address) For Each anyEntry In searchList If UCase(Trim(anyEntry)) = findEntry Then 'name matched, check formatting If anyEntry.Font.ColorIndex < whatColor And _ anyEntry.Font.Strikethrough = False Then anyEntry.Activate Set searchList = Nothing CommandButton1.Visible = False CommandButton3.Visible = True Exit Sub ' all finished End If End If Next End If Else 'did not have last name, do we have 'a first name to search for findEntry = UCase(Trim(Me.TextBox2)) If findEntry < "" Then 'have a first name to look for 'look in column C for it Set searchList = ActiveSheet.Range("C1:" & _ ActiveSheet.Range("C" & Rows.Count).End(xlUp). _ Offset(1, 0).Address) For Each anyEntry In searchList If UCase(Trim(anyEntry)) = findEntry Then 'name matched, check formatting If anyEntry.Font.ColorIndex < whatColor And _ anyEntry.Font.Strikethrough = False Then anyEntry.Activate Set searchList = Nothing CommandButton1.Visible = False CommandButton3.Visible = True Exit Sub ' all finished End If End If Next End If End If End Sub Private Sub CommandButton2_Click() 'the "cancel" button Unload Me End Sub Private Sub CommandButton3_Click() 'Do exactly the same again, but start it from the active cell End Sub Private Sub UserForm_Initialize() Me.TextBox1 = "" ' last name box Me.TextBox2 = "" ' first name box End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repeat Button | Excel Programming | |||
repeat row button | Excel Worksheet Functions | |||
repeat row button | Excel Programming | |||
Create floating button based on button click in menu | Excel Programming | |||
How do I get the "repeat" button on a toolbar? | Excel Discussion (Misc queries) |