PDA

View Full Version : Sleeper: How to assign macros from workbook module to Option Buttons Automatically



Shazam
09-28-2005, 05:06 PM
Good Afternoon,

I have this workbook that I run a simple macro on it and it also import a module to the workbook. The module contains 4 macros. The problem is I have 4 Option Buttons on the workbook and I assigning each button from the 4 macros of the module. I thought by recording a simple macro and assigning those macros to the Option Buttons will be fine. But the next day I ran the macro it did not assign the 4 macros to the 4 Option Buttons. Why is that ? Is there something I'm missing ?

Here is the code that I ran a simple macro to assign to the Option Buttons.



Sub Assing_Option_Buttons()
ActiveSheet.Shapes("Option Button 1").Select
Selection.OnAction = "Sort_Wip"
ActiveSheet.Shapes("Option Button 2").Select
Selection.OnAction = "Sort_Fgs"
ActiveSheet.Shapes("Option Button 3").Select
Selection.OnAction = "Highlight_Negatives"
ActiveSheet.Shapes("Option Button 4").Select
Selection.OnAction = "Sum_Negatives"
Range("F5").Select
End Sub


I'm getting complaints from the users they said when they select the option buttons it gives them a message that personal.xls does not exist. Is there a certain code that it will assing thoses macros from the workbook module to the correct Option Buttons on the worksheet ?

royUK
09-28-2005, 10:13 PM
I don't understand why you say you need to imprt Modules. This task is better suited to a CommandButton, you shouldn't need to assign the macros each time. See attachment - I have substituted a CommandButton from the Controls toolBox

Shazam
09-29-2005, 04:48 AM
Thank You for replying,

This code you provided wont work well for me.



Private Sub CommandButton1_Click()
Call Module1.Sort_Wip
End Sub


The reason is I generate this report daily so I would have to implement your code manually everyday. The problem is not importing the module its assing the macros to the Option Buttons automatically. So all I have to do is just run the code and it will automatically assing the macros to the Option Buttons.

Do you have any ideas ?

royUK
09-29-2005, 04:58 AM
I would create an addin with a ToolBar

Shazam
09-29-2005, 05:12 AM
I did a addin before but getting the macro to assing the option buttons is problem.

Is there a code can do it ?

Marcster
09-29-2005, 05:18 AM
One idea is:
Create a button on a toolbar which when clicked on
will run the macro. Tools > Customize

Another idea is:
Place the code in Workbook_Open() so the code gets run everytime the workbook is opened. Double-Click ThisWorkbook in the VBE. Private Sub Workbook_Open

Marcster.

Marcster
09-29-2005, 05:21 AM
Double-Click on 'Thisworkbook' in the VBAProject in the VBE
Then place this code:


Private Sub Workbook_Open()
ActiveSheet.Shapes("Option Button 1").Select
Selection.OnAction = "Sort_Wip"
ActiveSheet.Shapes("Option Button 2").Select
Selection.OnAction = "Sort_Fgs"
ActiveSheet.Shapes("Option Button 3").Select
Selection.OnAction = "Highlight_Negatives"
ActiveSheet.Shapes("Option Button 4").Select
Selection.OnAction = "Sum_Negatives"
Range("F5").Select
End Sub


The above code will run everytime the workbook is opened.

Marcster.

Shazam
09-29-2005, 06:14 AM
Thanks Marcster,

The code you provide is great. But I import my report into excel daily, its a different workbook everyday. Is there a code that it will place your code in the workbook instead of me dong manually ?