• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1167
  • Last Modified:

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)

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

Open in new window

SearchTest.xls
0
upsfa
Asked:
upsfa
  • 3
1 Solution
 
McOzCommented:
You need to end the sub with "End Sub".

Cheers
0
 
upsfaAuthor Commented:
Sorry, cut and paste error.  My code does have End Sub int it.
0
 
Saqib Husain, SyedEngineerCommented:
change

Call FindProject

to

Call Sheet1.FindProject
0
 
upsfaAuthor Commented:
Perfect!  Thank you.
0
 
upsfaAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now