Solved: Can it be done ??? Excel VBA help

[snoozee]

HI

I have a UserForm that has a ComboBox and a CommandButton. The data in the ComboBox varies depending on what the user has typed, However no matter what "shows" it links to a certain worksheet.

Example

ComboBox shows "Management" this would link to a worksheet called "Quarter1".

What I need to try and work out is how to code the CommandButton so that if the user chooses "Management" then the WorkSheet "Quarter1" would open.

Any help would be greatly appreciated

 

[CDHarm]

Hi,

Can you supply an example of your file?

 

[snoozee]

The coding I need is linked to the Userform1 and this userform is activated by clicking the "View Development Plan" commandbuttton on the "Start" worksheet.

Appreciate you l;looking at this for me as it's the last piece I need to complete - coding wise

 

[Keebellah]

The example contains 'broken' links due to a file named 'Small Screen Development Plan' no go to test.

 

[snoozee]

Sorry was in a rush, I'll redo and re attach

 

[snoozee]

For some reason the file had a hissy fit on my work computer so have had to wait until I got home to recreate it.

This version is correct

 

[Keebellah]

Yes, probably is but I still get the message that the link is missing:

The file keeps referring to a file named Small Screen Development Plan V2.xls

Is this the original name for this example file?
BTW why is the link file xls while it seesm that you're using 2007 or newer?

 

[Keebellah]

It's not working here, invalid reference, etc etc and the View button is inactive too.

 

[snoozee]

HI All

OK, the problem appears to be that at work I work off Office 2010 and Home is 2013. After doing some reading it also appears that the Microsoft update that went through means that all of the "Active X" controls that I have used won't work and throw up errors.

When I get home tonight I will redo the example in 2013 and replace all of the Active X controls with Form controls, then hopefully everything will behave as it's meant to

I really appreciate you guys "trying" to look at this for me.

 

[snoozee]

OK, have triple checked this one and replaced all of the CommandButtons with Buttons. It is also in 2013.

 

[Keebellah]

Okay, it works now.
I'll check it out during the day and see what I can do to help
I'm working with 2010.

 

[snoozee]

Thanks heaps as I've just spend the last 4 hours replacing all of my Active X controls and reassigning all of the code. I've emailed it to work so fingers crossed it now works - apart from the one piece of code that you are looking at of course.

 

[Keebellah]

Hi, just add the following piece of code to you userform code:

Private Sub CommandButton1_Click()
If Me.ComboBox1.Value = "" Then Exit Sub
Sheets(Me.CommandButton1.Caption).Select
Unload Me
End Sub

I did however have to correct the userform and remove the space after the text 'Quarter 1 ' which you have in the command button's caption text.
Rule of thumb Never add unnecessary spaces

Just in case I've attached the modified sample file for you

Hope it works there

 

[snoozee]

HI

Really appreciate your help with this, yet it's not entirely what I needed

As there is 3 options in the ComboBox I need to code the "View" button so that depending on which option is chosen the relevant sheet opens.

As the three options can vary I need to link the code to open the "actual" WorkSheet name - refer to the "Data" sheet as WorkSheet names are listed to the right.

You can ignore the "Quarter" buttons as they relate to something else.

Sorry to be a pain and you'll be pleased to know that it works at work

 

[Keebellah]

Just change the code so that it acts when you press the view button and use the caption of the Quarter ? button to activate the sheet.

Private Sub CommandButton4_Click()
If Me.ComboBox1.Value = "" Then Exit Sub
Sheets(Me.CommandButton1.Caption).Select
Unload Me
End Sub