PDA

View Full Version : Solved: How to call a macro in for - next



domfootwear
01-19-2011, 12:58 AM
Can you help me to check my macro, I try below code, but can not work.

Private Sub CommandButton1_Click()
Dim i As Integer
Dim m As String
For i = 1 To 4
If Controls("OptionButton" & i).Value = True Then Application.Run ("'" & ThisWorkbook.Name & "'!O" & i)

Next

End Sub
Sub O1()
MsgBox "OptionButton1 !"

End Sub
Sub O2()
MsgBox "OptionButton2 !"

End Sub

Sub O3()
MsgBox "OptionButton3 !"

End Sub

Sub O4()
MsgBox "OptionButton4 !"

End Sub

GTO
01-19-2011, 02:58 AM
Where are the commandbutton and option buttons, in a userform?

domfootwear
01-19-2011, 03:05 AM
Where are the commandbutton and option buttons, in a userform? Yes, commandbutton and option buttons in Userform

Thanks so much

GTO
01-19-2011, 05:16 AM
Sorry, a bit busy and I wanted to search a bit as well. I don't regularly use .Run, so wanted to check a couple of things.

First, you seem to have produced an anomaly I was unaware of. In a Standard Module:
Sub ex01()
Application.Run "M"
End Sub

Sub M()
MsgBox "Made it to M"
End Sub

Sub ex02()
Application.Run "M1"
End Sub

Sub M1()
MsgBox "Made it to M1"
End Sub

Sub ex03()
Application.Run "M1mmm"
End Sub

Sub M1mmm()
MsgBox "Made it to M1mmm"
End Sub
If you step through these, 'ex01' succeeds, as does 'ex03', but 'ex02' fails! I did not find any documentation on this, but it appears that if using Appplication.Run, wherein the called procedure's name contains a number, the called procedure's name must be at least three characters in length. I certainly am not claiming this as fact, but that's my observation.

To fix this for the moment, lets just use 'OOPS1' through 'OOPS4' for your called procedure names.

Now we run into a second problem, as you will see that you still receive the 'not found...' error. This is because you have Application.Run trying to call procedures housed in the UserForm, which is basically a Class Module. You might be able to reference it, but I see neither how, nor why we'd want to. Instead, just move the called Subs to a Standard Module.

Does that help?

Mark

domfootwear
01-19-2011, 05:53 PM
Sorry, a bit busy and I wanted to search a bit as well. I don't regularly use .Run, so wanted to check a couple of things.

First, you seem to have produced an anomaly I was unaware of. In a Standard Module:
Sub ex01()
Application.Run "M"
End Sub

Sub M()
MsgBox "Made it to M"
End Sub

Sub ex02()
Application.Run "M1"
End Sub

Sub M1()
MsgBox "Made it to M1"
End Sub

Sub ex03()
Application.Run "M1mmm"
End Sub

Sub M1mmm()
MsgBox "Made it to M1mmm"
End Sub
If you step through these, 'ex01' succeeds, as does 'ex03', but 'ex02' fails! I did not find any documentation on this, but it appears that if using Appplication.Run, wherein the called procedure's name contains a number, the called procedure's name must be at least three characters in length. I certainly am not claiming this as fact, but that's my observation.

To fix this for the moment, lets just use 'OOPS1' through 'OOPS4' for your called procedure names.

Now we run into a second problem, as you will see that you still receive the 'not found...' error. This is because you have Application.Run trying to call procedures housed in the UserForm, which is basically a Class Module. You might be able to reference it, but I see neither how, nor why we'd want to. Instead, just move the called Subs to a Standard Module.

Does that help?

Mark

Thanks so much for your help :clap::clap::clap:

GTO
01-20-2011, 03:32 AM
The macro I want to do is call one of the six macros that we all do the same but with different data. The main macro is used to call one of the six. I do not know how to do this by recording a new macro to call one of the six. That means I have to create 6 new macros and may also adhere to already have six. The reason I wanted a macro to call one of the six is that the macro could be changes if needed instead of having to make the same change to six individual macros.

Greetings,

I would suggest you start your own thread, and attach a made-up example workbook with what we might run into and what is wanted.

Mark

GTO
01-20-2011, 03:33 AM
Thanks so much for your help :clap::clap::clap:

You are very welcome:)

kvnfrnk
01-20-2011, 03:53 AM
Greetings,

I would suggest you start your own thread, and attach a made-up example workbook with what we might run into and what is wanted.

Mark
Ok I will, Thanks for the reply.. for The macro I want to do is call one of the six macros that we all do the same but with different data. The main macro is used to call one of the six. I do not know how to do this by recording a new macro to call one of the six. That means I have to create 6 new macros and may also adhere to already have six. The reason I wanted a macro to call one of the six is that the macro could be changes if needed instead of having to make the same change to six individual macros.

payday loan online (http://www.lightpaydayloan.com/)

GTO
01-20-2011, 03:56 AM
Whoa!

Aussiebear has already done this for you :-) I spoke too soon :-(

Your new thread is here: http://www.vbaexpress.com/forum/showthread.php?t=35768