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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
272
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 :)
 

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
272
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 :)
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,636
The example contains 'broken' links due to a file named 'Small Screen Development Plan' no go to test.
 

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
272
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 :)
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,636
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

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,636
It's not working here, invalid reference, etc etc and the View button is inactive too.
 

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
272
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

Thread Starter
Joined
Apr 10, 2006
Messages
272
OK, have triple checked this one and replaced all of the CommandButtons with Buttons. It is also in 2013.
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,636
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

Thread Starter
Joined
Apr 10, 2006
Messages
272
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

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,636
Hi, just add the following piece of code to you userform code:

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
 

Attachments

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
272
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

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,636
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.

Code:
Private Sub CommandButton4_Click()
If Me.ComboBox1.Value = "" Then Exit Sub
Sheets(Me.CommandButton1.Caption).Select
Unload Me
End Sub
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top