Calling Module Sub from Userform Button Click

Salrandin

New Member
Joined
Jun 27, 2011
Messages
32
Hi All,

I want to call a sub called "findEvents(date1, date2)"
from a userform button click but am getting the "sub or function undefined error" this is Excel 2003

Code:
Private Sub Create_Click()
    Dim startDay, endDay As Date
    If MM1CB <> "" And MM2CB <> "" And DD1CB <> "" And DD2CB <> "" And StartHourCB <> "" _
       And EndHourCB <> "" And StartMinuteCB <> "" And EndMinuteCB <> "" And YY1CB <> "" _
       And YY2CB <> "" Then
 
        startDay = MM1CB & "/" & DD1CB & "/" & YY1CB & " " & StartHourCB & StartMinuteCB
        endDay = MM2CB & "/" & DD2CB & "/" & YY2CB & " " & EndHourCB & EndMinuteCB
 
        Call findEvents(startDay, endDay)
 
    Else
       MsgBox "All Fields Required"
    End If
End Sub

I have tried using:

Call Sheets("Data").findEvents(startDay, endDay)

instead but that also gives me an error. What am I missing? Thanks.
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
As far as I know, you can't. If a userform is active, subroutines are paused (and therefore can't be started either)

Instead you could use a tag. Replace Call findEvents(startDay, endDay) with
Code:
Tag = 1
Hide
Then, in the subroutine that shows the form in the first place, have:

Code:
If whateveryourformiscalled.Tag = 1 Then
 Call findEvents(whateveryourformiscalled.startDay, whateveryourformiscalled.endDay)
End If
 
Last edited:
Upvote 0
Chances are you have placed findEvents() someplace its not easily visible, such as a sheet's module. If that is it, move it to a Standard Module.
 
Upvote 0
As far as I know, you can't. If a userform is active, subroutines are paused (and therefore can't be started either)

Hi there,

That is a little off. Create a userform w/one button in a new wb.

Userform code:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Call</SPAN> mysub(20)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Standard Module code:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> mysub(NumberPassed <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>)<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> NumberPassed<br>        Cells(i, 1).Value = i * NumberPassed<br>    <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> AStart()<br>        <br>    UserForm1.Show<br>    MsgBox "Won't run til form dismissed"<br>    <br>    UserForm1.Show vbModeless<br>    MsgBox "still runs"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

As you will see, it is IF the form is Modal, it stops executing in the procedure that called .Show.

If vbModeless, the code continues, and if a button on the form is pushed, the code callled executes regardless of modality.
 
Upvote 0
Ah yes! My mistake. I remember now, previously reading here about Modeless forms (probably in answer to one of my own questions, lol)
 
Upvote 0
If the code module for Sheets("Data") is where findEvents is, you can call it with the CodeName for Sheets("Data").

Code:
Call Sheet2.findEvents(startDay, endDay)
You may have to explicitly declare the Sub findEvents as Public.
 
Upvote 0
Will it make a difference if I unload the userform after I am done executing the Macro?

Also, I did not mention, when I use

Call Sheets("Data").findEvents(datea,dateb)

it gives me a:

Runtime Error 13
Type Mismatch

and highlights the Call FindEvents line.

And yes I believe it is in a sheet module.. I will look into this standard module.
 
Upvote 0
If the code module for Sheets("Data") is where findEvents is, you can call it with the CodeName for Sheets("Data").

Code:
Call Sheet2.findEvents(startDay, endDay)
You may have to explicitly declare the Sub findEvents as Public.

I declared it as public and tried this but It also gives me the runtime error 13.
 
Upvote 0
...I have tried using:

Call Sheets("Data").findEvents(startDay, endDay)

instead but that also gives me an error. What am I missing? Thanks.

If the code module for Sheets("Data") is where findEvents is, you can call it with the CodeName for Sheets("Data").

Code:
Call Sheet2.findEvents(startDay, endDay)
You may have to explicitly declare the Sub findEvents as Public.

ACK! Thanks Mike, I certainly missed that :-(

I declared it as public and tried this but It also gives me the runtime error 13.

Hi again,

In Mike's example, Sheet2 is representative, not literal. Right-Click the tab of Data and select 'View Code'. Now look at the properties window. See how it has two Name properties? The top one (between the parenthesis) is the CodeName of the sheet; use that.

Mark
 
Upvote 0
ACK! Thanks Mike, I certainly missed that :-(



Hi again,

In Mike's example, Sheet2 is representative, not literal. Right-Click the tab of Data and select 'View Code'. Now look at the properties window. See how it has two Name properties? The top one (between the parenthesis) is the CodeName of the sheet; use that.

Mark

Hehe I did understand that part it was sheet3 for me still the runtime error 13 though...

However, I have fixed it! I made it into a standard module and made it public, now it works thanks for the help! :)
 
Upvote 0

Forum statistics

Threads
1,223,521
Messages
6,172,815
Members
452,482
Latest member
Maverick007

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