Code problem

**Aussie_Striker** · 10-21-2006, 11:34 PM

I have this code on a combobox but for some reason it comes up with an error. It is supposed to go to the sheet that is the value selected in the combobox.

Private Sub MapPlayers_Change()
Sheets(MapPlayers.Value).Select
End Sub

Could it be because it is always trying to go there because there is a value selected even before someone clicks on it? Is there something I need to add to the code to prevent that?

**davesexcel** · 10-22-2006, 01:15 AM

When you make a selection in the combobox are you supposed to go to a certain range??

**Aussie_Striker** · 10-22-2006, 02:16 AM

It The list is referenced from a range but when you make the selection it only needs to go to the sheet that is the same name as the value in the list. I have had it work but it seems to throw an error without even selecting anything now.

**Mallycat** · 10-22-2006, 04:39 AM

Why don't you enter another line of debug code before the suspect line. Something like x= MapPlayers.Value
Then put a break on this line of code, and see what x = before proceding. This may tell you what is wrong

**davesexcel** · 10-22-2006, 08:44 AM

"Could it be because it is always trying to go there because there is a value selected even before someone clicks on it? Is there something I need to add to the code to prevent that?"

where do you have the code, It sounds like you assigned a macro to the combobox

Anyway the code should be this to activate another sheet

Please Login or Register  to view this content.

**Aussie_Striker** · 10-22-2006, 09:45 AM

I just put the code in from selecting view code on the combobox. I actually copied it from code from a button that said the same. Seeing that the combobox had a value I made that the code as shown in my first post. I will try changing select to activate and see if there is any difference. The only way I have so far been able to get around it is by reverting back to having a button that uses the same code (associated to the button of course) to select the value of the combobox. I don't have the problem at all while using that method.

On the debug suggestion. I haven't ever used debugging so that is a new thing for me. Will have to learn a bit (or a lot) I think.

**Aussie_Striker** · 10-28-2006, 03:59 AM

Originally Posted by davesexcel
[/B

where do you have the code, It sounds like you assigned a macro to the combobox

Anyway the code should be this to activate another sheet

Please Login or Register to view this content.

That one doesn't work at all. Keeps taking me to break mode on that line.

**Carim** · 10-28-2006, 04:44 AM

Hi,

Do you mean something along these lines ...

Please Login or Register  to view this content.

HTH
Carim

**Aussie_Striker** · 10-28-2006, 07:08 AM

Yes but whatever the combobox value is is the sheet that it selects. The code you have looks to me like you are selecting a certain sheet (correct me if I'm wrong). Currently I am using the combobox and a seperate button with the button having code

Private Sub CommandButton1_Click()
Sheets(NavigationMenu.Value).Select
End Sub

NavigationMenu being the combobox name.

Previously on the combobox I had

Private Sub NavigationMenu_Change()
Sheets(NavigationMenu.Value).Select
End Sub

That worked to click on the combobox and it would take the selection you pick straight away which was good. Unfortunately then it started going haywire.

Maybe the '_Change' should be different. It would be good if it worked without error but for now I am just using the button method.

**davesexcel** · 10-28-2006, 08:04 AM

Here is one of my favorite codes from the Pearsons, when you run the macro, the sheet list pops up and then you can select the sheet you want to go to and there you go....

just another option I wanted to share with you...

Please Login or Register  to view this content.

**Aussie_Striker** · 10-28-2006, 08:40 AM

Thats pretty neat. Is there anyway to change it so it only selects certain sheets. It isn't very useable in my application as I have 70 sheets in use and only 17 of those are the ones I need as the selectable ones for the above code. Also I am refencing cell values from the different sheets into the cells with the formulas. So although it is neat, doesn't really help in this scenario. Might be useful for some other applications though.

**davesexcel** · 10-28-2006, 09:53 AM

Here is a working example I had aquired from somewhere at one time, I also know I have a post somewhere that refers to this subject as well but I can't find it

You will be able to get the codes from the example

**Aussie_Striker** · 10-28-2006, 10:25 AM

Im using Excel 2003, don't know if that is why but that macro doesnt work on here either. This is the code it had

Please Login or Register  to view this content.

As I have inserted into the code note where it stops in error

**davesexcel** · 10-28-2006, 10:39 AM

I just thought of an easy way to get this to work, and you can use data validation for your drop down menu

you could use an if statement in the worksheet module so whenever you select an item in the menu the code kicks in how about

Please Login or Register  to view this content.

the above code goes into the worksheet module

now the codes below go into a regular module

Please Login or Register  to view this content.

I hope this idea helps you out

**Aussie_Striker** · 01-10-2007, 09:10 PM

Thanks I hadn't read this reply till today as I have been using the button method again. The code certainly looks useful possibly even for other things I may implement.