Assign macro to combobox selections

[legepe]

Hi all, Im brand new here, and a bit of a novice when it come to macros and VB...
I need to assign a combobox to macros
Ive tried everythng I can think of but with no success!!!
I cannot get this formula to work can anyone assist?
I have this in a different module to where tha actual macros are. Is this correct? (although I?ve tried both)
Thanks
legepe


Private Sub ComboBox64_Change()
Select Case Me.ComboBox64.Value

Case "ENE"
Macro1

Case "FEB"
Macro2

Case "MAR"
Macro3

End Select
End Sub

[compariniaa]

How did you create your combobox? from the Forms toolbar, or the Control Toolbox toolbar?

[compariniaa]

or is it in a userform?

[legepe]

I think it is the forms tool bar
The one at the top of the screen

[compariniaa]

if you made it using the forms toolbar and it's embedded in a sheet, i can't see any reason why you wouldn't be able to assign a macro to it. but if it was made with the control toolbox or if it's in a userform, right-click the box, click "View Code" and copy and paste your code in there

[legepe]

I can assign a macro to the combobox, but I have 12 macros and 12 values in the combobox - each one must be assigned independantly
The only thing that I can get to work is one macro (one of the 12) And no matter which value I click on it just uses the one macro

[legepe]

The code in View Code is where it is linked to the values that are in the combobox, I do not understand why or where i need to copy these to?

[Bob Phillips]

I can assign a macro to the combobox, but I have 12 macros and 12 values in the combobox - each one must be assigned independantly
The only thing that I can get to work is one macro (one of the 12) And no matter which value I click on it just uses the one macro

Link the comobox to a cell, the selected index then goes in that cell. In your macro, test which value is in the cell, and use that to index into the list you use to poulate the combobox, you can then call the macro based upon that value.

[lucas]

Hi legepe,
I have edited your post to reflect the question you are asking. Please help isn't very descriptive of your problem. I also added vba tags to your code in your first post....select your code when posting and hit the vba button. It makes your code easier to read.

I would suggest that you upload a sample of your spreadsheet so that others can look at it and see what is going on, it makes it easier to understand your problem.

[legepe]

Thanks for all your replies... and your comments
I have tried to upload the spread sheet its in Gif format, but way to big to post here
Can you tell me the best format to do this in?
legepe

[compariniaa]

you could zip it, or just post the workbook itself. if the workbook is too big just take some rows off...we just need to get an idea, not necessarily an exact picture

[legepe]

Please see attached file

I am working with a spanish version of excel which is making things more difficult!

1. I have doubts about the name of the "combobox" This is displayed in the top left hand side of the screen when I right click on the combobox, Secondly the name displayed in the box is - Lista desplegable 64 - why 64, I do not have any more comboboxes attached to the sheet?

2. The 12 macros that I have made are for the blue tabs you see at the bottom of the screen shot

3. I need a way to link these 12 macros to each of the tabs, so that when I open the combobox and click on JUN for example, it will take me to that sheet.

I hope that this is clear, and I am hoping that someone can help me?

Thanks a lot

legepe

[Bob Phillips]

Post the workbook, not a picture of it.

[legepe]

I think I must be misunderstanding - the workbook is far to large
How can I post it?

[legepe]

Ive done it !!!
Please find the attached workbook
Hope you can help
Thanks
legepe

[Bob Phillips]

Exactly as I said earlier

[legepe]

Thats Fantastic!!
Can you believe that I?ve spent 4 days trying to figure that out
Thanks a lot
legepe

[Bob Phillips]

You should have posted earlier, it took me 10 mins.

[legepe]

Well, Ill know next time
Thanks again!!
Can

[legepe]

---Can you recommend a good way of learning VB... may be a book but for dummies!!!

[Bob Phillips]

---Can you recommend a good way of learning VB... may be a book but for dummies!!!

Exactly that, http://www.j-walk.com/ss/books/bookxl27.htm.

Also, look at http://www.mvps.org/dmcritchie/excel/getstarted.htm

[legepe]

Hi, I?m back!
got still a problem with it
For example when you choose MAY then return to the sheet with the combobox, I cannot go directly back to May, I have to choose another month then go back and click on MAY
How can I solve this problem?
Thanks
legepe

[Bob Phillips]

Hi, I?m back!
got still a problem with it
For example when you choose MAY then return to the sheet with the combobox, I cannot go directly back to May, I have to choose another month then go back and click on MAY
How can I solve this problem?
Thanks
legepe

That's the way that DDs work, you have to select something to activate the macro.

We could trap the sheet activate event to reset the combo back to clear, it will at least make it it obvious that a new selection is required.

[legepe]

I think that would do the trick
Can you help me with it?

[Bob Phillips]

Here you are.

[legepe]

Thanks for that, it works a treat!!
Just "one" more problem!!
I have hidden the sheets that are in the combobox, and now it won?t work
What needs to be done to the formula now?
Thanks
legepe

[legepe]

I used format / page / hide for this

[Bob Phillips]

.

[legepe]

Hi,
The main work sheet that I am working on has 49 pages in total
When I put all the formulas in and address them to each page then try to process a macro it gives me the following error-


Private Sub Worksheet_Activate()
Dim sh As Worksheet
Application.ScreenUpdating = False

For Each sh In ThisWorkbook.Worksheets
If sh.Name <> Me.Name Then
sh.Visible = xlSheetHidden
End If
Next sh

Application.Range(Me.DropDowns("MonthDD").LinkedCell).Value = 0

End Sub


Where i have underlined it, it is shown in yellow

If I try to do it again it gives me an error message " Cannot exicute code in interuption mode" This is how ive translated it???

Can you help me some more

Thanks

legepe

[Bob Phillips]

Hi,
The main work sheet that I am working on has 49 pages in total
When I put all the formulas in and address them to each page then try to process a macro it gives me the following error-


Private Sub Worksheet_Activate()
Dim sh As Worksheet
Application.ScreenUpdating = False

For Each sh In ThisWorkbook.Worksheets
If sh.Name <> Me.Name Then
sh.Visible = xlSheetHidden
End If
Next sh

Application.Range(Me.DropDowns("MonthDD").LinkedCell).Value = 0

End Sub

Where i have underlined it, it is shown in yellow

If I try to do it again it gives me an error message " Cannot exicute code in interuption mode" This is how ive translated it???

Can you help me some more

Thanks

legepe

How can you have 49 sheets, there are only 12 months?

[legepe]

By the way, I made another 3 comboboxes totaling 4 for the 48 pages
12 in each...

[legepe]

there are 4 seperate sets of data that relate to the main sheet

[legepe]

Hi, I cut the sheet down in size because I thought it would be too big otherwise. Im sorry if ive made things difficult, I thought that i would have been able to complete this with what info.. you gave me in the other sheet

Find the attached sheet with 49 pages (zipped)

legepe

[legepe]

Thanks a lot for all your help
Just to let you know Ive posted this on Exceltip
Thanks again....
legepe

[Bob Phillips]

Nice to know that you don't trust me to solve it.

[legepe]

Hey great to see you back, its not that i dont trust you...
It was my frustration trying to finish this and thought that I had upset you!! as you stoped replying
Anyway, thanks a lot!!!!!
legepe

[Bob Phillips]

Hey great to see you back, its not that i dont trust you...
It was my frustration trying to finish this and thought that I had upset you!! as you stoped replying
Anyway, thanks a lot!!!!!
legepe

No, I just need a break like everyone else. Don't forget, everyone may not be on your time zone.

[Bob Phillips]

BTW, you should go back to ExcelTip and post that it has been solved, avoid wasting time of other people.

[Beau_Lang]

I encountered the same problem where if you select A, then return to the main menu sheet with the combox/listbox, you cannot go to A again, even if you click on A again. You will need to select B, then select A again.

Another problem I have is if A is currently highlighted in the combox/listbox, and you click anywhere on the scrollbar or arrow to see other selections, Excel brings you straight to Sheet A. Then you will need to go back to main menu again. Very tedious. :banghead: Was wondering if the previous:
Application.Range(ActiveSheet.DropDowns("MonthDD").LinkedCell).Value = 0
would also solve the problem.

Pls advise.