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: list box selection to run macro

Discussion in 'Business Applications' started by mrl, Jan 11, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. mrl

    mrl Thread Starter

    Joined:
    Aug 12, 2007
    Messages:
    327
    Hi,
    I'm trying to have a box come up with a list and depending of the selection, run a macro. I have the following, but I'd like to have the list in the box instead of on the sheet. I'm new to boxes, so I hope I'm using the right terminology

    HTML:
    Sub test()
    On Error Resume Next
    InputCells = Application.InputBox(Prompt:="What Month", Type:=2)
       If InputCells = vbNullString Then
        Exit Sub
        Else
        Select Case InputCells
        Case "January"
        Janu
        Case "February"
        Febu
    End Select
    End If
    End Sub
    
    
    
    Sub Janu()
    Application.Goto Reference:="january"
    End Sub
    
    
    
    Sub Febu()
    Application.Goto Reference:="february"
    End Sub
    
    Mike
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    List in the box, do you mean a user form?
     
  3. mrl

    mrl Thread Starter

    Joined:
    Aug 12, 2007
    Messages:
    327
    Hi,
    First, happy New Year
    I know nothing about user forms
    Way back in my Lotus days, I could have a box come up with choices. (it was called {menubranch}) When I clicked one, a macro associated with it would run
    With the code that I showed you, the list is on the spreadsheet and when I click on a choice, the item goes into the box,or I type it myself into the box then the macro runs. I was trying to have the choices in the box. This would give me another learning tool. :D
    If what I have to use is a user form, I'll have to learn about them.

    Mike :)
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Hi Mike, happy New Year to you too.
    I think I know what you mean.

    I'll sleep over it. It's bedtime here now. If you have a simple sample you're using then attach it, okay?

    Read it tomorrow.
     
  5. mrl

    mrl Thread Starter

    Joined:
    Aug 12, 2007
    Messages:
    327
    Hi, Hans
    Happy dreams :D

    I attached a sample file. Rollin_again helped me with the code (Thank you Rollin_again, I'm greatful) awhile back.


    Mike
     

    Attached Files:

  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    As long as you can get the correct text to appear in your specific cell you can use an embedded button on the worksheet to fire the macro code. You can even get the code to fire automatically anytime the value changes in a designated cell. The other option is to create your own userform which can be displayed with a listbox to make your selection from and then a button to submit which will fire the code.

    Open the workbook and press ALT + F11 to open the VB editor. You can then click INSERT >> USERFORM to create the blank form. You can resize the form by dragging the corners and and you should see a control box appear next to the form that will allow you to add whatever controls you want (textboxes, listboxes, buttons, labels, etc.) Play around with the form and put something together and we can tell you how to add the code.


    Rollin
     
  7. mrl

    mrl Thread Starter

    Joined:
    Aug 12, 2007
    Messages:
    327
    Hi Rollin
    How's the weather? We had about 4" of white stuff (lower Mich) Tues and Weds
    I played :D with the user form and was able to get the contol buttons to work (calling up a macro)
    I can't get the list box to do the same thing. I'm attaching a sample file
    Thanks in advance for all the help :D
     

    Attached Files:

  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Do you want a specific macro to run based on the selection, or would it be preferred to have one generic macro run where you pass a variable to it from the selection? The benefit this would have is you wouldn't have to have a bunch of macros to run based on the selection, you'd only need one.
     
  9. mrl

    mrl Thread Starter

    Joined:
    Aug 12, 2007
    Messages:
    327
    Hi Zack

    If I highlight January, and hit OK I want to goto Range name "January"..If I highlight February, and hit OK I want to goto Range name "February", Etc. I Already figured out how to do it will command buttons in the userform (userform1 on the sample sheet). Now I'm trying to learn how to do it with a list box in the userform. (userform2)



    Mike
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Well this would be the basics...

    Code:
    Option Explicit
    
    'This is the GO button
    Private Sub CommandButton1_Click()
        Dim i As Long
        For i = 0 To Me.ListBox1.ListCount - 1
            If Me.ListBox1.Selected(i) = True Then
                Application.Goto (Me.ListBox1.List(i))
                Exit For
            End If
        Next i
    End Sub
    
    'This is just code for me to temporarily load a listbox with all month names
    Private Sub UserForm_Initialize()
        Dim i As Long
        For i = 1 To 12
            Me.ListBox1.AddItem MonthName(i, False)
        Next i
    End Sub
    
    'This is the cancel button
    Private Sub CommandButton2_Click()
        Unload Me
    End Sub
    Is this what you're looking for?
     
  11. mrl

    mrl Thread Starter

    Joined:
    Aug 12, 2007
    Messages:
    327
    Being that I don't know anything about userForms, I hope you don't mind my questions ;)
    I was using the following code to bring up the userform (userform2 in the sample)
    HTML:
    Sub form2()
    UserForm2.Show
    End Sub
    I put your codes in the properties section of UserForm2 after I clicked on that userform in the VBA window, and I deleted what I was trying to get to work. Now when I try to use my code to bring up the user form, I get an error message. something is wrong with "Userform2.show".
    I have code for each name (month) in a module1.
    Am I putting your code in the wrong place?
    I don't know how to get the userform to show without writing code to show it
    I don't know how to use the ....Sub UserForm_Initialize()
    I don't know how make any of the "Private Sub....codes to work


    I started out making command buttons on the sheet but I'm trying to make it easier.
    I then used Rollin's response and was able to make command buttons on the userform and have them work'
    Now I'm trying to learn how to make a list on the userform to start a macro depending on the choice
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    If you put the code in your userform's code module (of UserForm2), you would need to make sure and either change the code sub routines names to match your controls, or change your controls names to match that of the code. So the 'GO' button in my code is the CommandButton1, and the 'Cancel' button is the CommandButton2. The userform initialize code is generic and will fit in any userform code module, but you can only have one per module. The only other thing you would need to check is the listbox name. I used the generic name, ListBox1. The code I posted doesn't need a routine for each month. Is there any way you can upload your file? Or strip it of any sensitive data and upload it?
     
  13. mrl

    mrl Thread Starter

    Joined:
    Aug 12, 2007
    Messages:
    327
    I made up a sample sheet with a short List for the list box

    I put your code in my userform's code module (of UserForm2), I changed my names to match yours. I left Userform1 in the spreadsheet so you can see if I have to tweek it. It works though :)

    I had to make a command button to bring up userform1 and userform2 because I don't know how to do it any other rway.

    Before I put in Private Sub UserForm_Initialize() etc. The command button for Userform2 would bring up the userform. Now it doesn't I probiably did something wrong. I don't know how to use it, either :confused:
     

    Attached Files:

  14. mrl

    mrl Thread Starter

    Joined:
    Aug 12, 2007
    Messages:
    327
    Hey Zack

    After I sent you the last reply, I took out the Private Sub UserForm_Initialize() thing and the userform worked like a charm :D :D

    I still would like to know the right way to bring up the form
    I would like to learn how to use the Initialize thing
    I would like to know why I didn't have to have a code for each month
    I know...too many questions ;)
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Try user form2 and see the code,
     

    Attached Files:

  16. Sponsor

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!

Loading...
Similar Threads - Solved list selection
  1. Gevans0666
    Replies:
    11
    Views:
    395
Thread Status:
Not open for further replies.

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

  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.
    Dismiss Notice