Solved

Excel 2010 – Call a subroutine from a macro

Posted on 2011-03-15
5
1,115 Views
Last Modified: 2012-05-11
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
Comment
Question by:upsfa
  • 3
5 Comments
 
LVL 9

Expert Comment

by:McOz
ID: 35141096
You need to end the sub with "End Sub".

Cheers
0
 
LVL 1

Author Comment

by:upsfa
ID: 35141422
Sorry, cut and paste error.  My code does have End Sub int it.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 35141535
change

Call FindProject

to

Call Sheet1.FindProject
0
 
LVL 1

Author Comment

by:upsfa
ID: 35147173
Perfect!  Thank you.
0
 
LVL 1

Author Closing Comment

by:upsfa
ID: 35147175
Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now