upsfa
asked on
Excel 2010 – Call a subroutine from a macro
I wrote (borrowed) some simple VB to take a variable from a text box in an Excel sheet and perform a search on the same sheet. It works fine when I used a command button to run it, but I want to run it from a macro and can’t seem to get it to work.
The command buttons look old fashon and cluncky as compared to the shapes that can be created with
Excel 2010. The shapes can only be used as buttons to run macros, however. So I want a macro to call the code to do the search.
(I saved the file down to Excel 2003 since not everyone has 2010)
The command buttons look old fashon and cluncky as compared to the shapes that can be created with
Excel 2010. The shapes can only be used as buttons to run macros, however. So I want a macro to call the code to do the search.
(I saved the file down to Excel 2003 since not everyone has 2010)
This is in Module 1
Sub RoundedRectangle2_Click()
Call FindProject
'Application.Run "FindProject"
End Sub
This is in Sheet1 module
Public Sub CommandButton1_Click()
‘ This runs fine from the Command button
Dim strFindWhat As String
strFindWhat = TextBox1.Text
On Error GoTo ErrorMessage
Cells.Find(What:=strFindWhat, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
Exit Sub
ErrorMessage:
MsgBox ("The data you are searching for does not exist")
End Sub
Private Sub FindProject()
‘ This code was copied from above and I try use a macro to call this sub – it doesn’t work
Dim strFindWhat As String
strFindWhat = TextBox1.Text
On Error GoTo ErrorMessage
Cells.Find(What:=strFindWhat, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
Exit Sub
ErrorMessage:
MsgBox ("The data you are searching for does not exist")
End
SearchTest.xls
ASKER
Sorry, cut and paste error. My code does have End Sub int it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Thank you.
ASKER
Thanks!
Cheers