Assign macro to combobox selections

[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-

[VBA]
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
[/VBA]

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-

[vba]
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
[/vba]
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. Was wondering if the previous:
Application.Range(ActiveSheet.DropDowns("MonthDD").LinkedCell).Value = 0
would also solve the problem.

Pls advise.