1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by snoozee, Feb 21, 2015.

Thread Status:
Not open for further replies.
Page 1 of 2
Advertisement
  1. snoozee

    snoozee It's My Birthday! Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    264
    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, Feb 21, 2015
    #1
  3. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    Can you supply an example of your file?
     
    CDHarm, Feb 21, 2015
    #2
  4. snoozee

    snoozee It's My Birthday! Thread Starter

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

    Attached Files:

    snoozee, Feb 21, 2015
    #3
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    The example contains 'broken' links due to a file named 'Small Screen Development Plan' no go to test.
     
    Keebellah, Feb 22, 2015
    #4
  6. snoozee

    snoozee It's My Birthday! Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    264
    Sorry was in a rush, I'll redo and re attach
     
    snoozee, Feb 22, 2015
    #5
  7. snoozee

    snoozee It's My Birthday! Thread Starter

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

    Attached Files:

    snoozee, Feb 23, 2015
    #6
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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, Feb 23, 2015
    #7
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    It's not working here, invalid reference, etc etc and the View button is inactive too.
     
    Keebellah, Feb 23, 2015
    #8
  10. snoozee

    snoozee It's My Birthday! Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    264
    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, Feb 23, 2015
    #9
  11. snoozee

    snoozee It's My Birthday! Thread Starter

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

    Attached Files:

    snoozee, Feb 23, 2015
    #10
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     
    Keebellah, Feb 24, 2015
    #11
  13. snoozee

    snoozee It's My Birthday! Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    264
    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. :)
     
    snoozee, Feb 24, 2015
    #12
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     

    Attached Files:

    Keebellah, Feb 24, 2015
    #13
  15. snoozee

    snoozee It's My Birthday! Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    264
    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 :)
     
    snoozee, Feb 24, 2015
    #14
  16. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
    Keebellah, Feb 24, 2015
    #15

  17. Sponsor

Page 1 of 2
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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1143509

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice