running macro from VBA in excel

[VBAmalta89]

I have recorded a macro in excel and i need it to run at a particular instant in a user form in VBA.

Is there any way of doing this?

Thanks

 

[jim may]

Define (what you mean here by) at a particular instant.

 

[Ruddles]

That depends on when the "particular instant" is. When the form loads? When a field on the form changes? When the user presses a button? At a particular time of day? When the form unloads?

 

[VBAmalta89]

So I have a dropdown menu in a user form which contains a list of options taken from an excel worksheet. Now the user will select one option. Each option is related to another set of lists in a worksheet.

What I want to do is when the user picks example option A a particular set of options are visible in the next drop down box (combo box), but if he selects option B in the first combo box, then the set of options in the second combo box are different and taken from another list.

Now I managed to achieve the above, however, what is happening is that in the second combo box the options aren't in order and tehre are a number of blank spaces between some of them. This is due to the way they are sorted in teh excel worksheet.

I created a macro to sort them with no spaces in the excel worksheet and I need this macro to run every time a particular option is made from the first combo box.

This is an example of the code I have currently:

Private Sub proc1_Change()
Select Case proc1.Value
Case Is = "Drilling": ZoneSelect1.RowSource = " 'Dropdown Menu Info'! D20:D27"
Case Is = "Turning": ZoneSelect1.RowSource = " 'Dropdown Menu Info'! E20:E27"

EndSelect
EndSub

Can you understand my problem?

 

[Ruddles]

Just call the other macro at the appropriate point of your code. Maybe something like this:-

Private Sub proc1_Change()

  Select Case proc1.Value
    Case Is = "Drilling": ZoneSelect1.RowSource = " 'Dropdown Menu Info'! D20:D27"
    Case Is = "Turning": ZoneSelect1.RowSource = " 'Dropdown Menu Info'! E20:E27"
  EndSelect
 
  [COLOR=red]Call other_macro[/COLOR]

End Sub

Is that what you mean?

 

[VBAmalta89]

yes...whts the coding i need to use to call the macro though?

 

[RoryA]

That was the coding...

 

[Ruddles]

yes...whts the coding i need to use to call the macro though?

As Rorya says, that - the bit in red - was the coding: it's the command Call followed by a space followed by the name of the other macro.

Hey, does Colonel G still own the Jerma Palace Hotel in Marsascala?

 

[VBAmalta89]

yep ok I managed.

@ruddles: the jerma hotel is long gone my friend it was demolished a few years back and its just an abandoned site nowadays!

 

[Ruddles]

Another memory consigned to the rubble of history!

I dived with Ray Ciancio from the dive centre next door to the Jerma Palace.