+ Reply to Thread
Results 1 to 11 of 11

~~ Help with Userform and Combo Box ~~

  1. #1
    Corey
    Guest

    ~~ Help with Userform and Combo Box ~~

    I have no experience with Userforms and combo boxes, but think they will
    assist me in achieving what i am trying to do.

    I have formatted a Userform with a combo box, but :

    How do i activate it from the worksheet ?

    I generally use the 'Asign macro' to buttons etc. BUT the userform and combo
    box does not show is the list of macro's.

    How do i asign the userform to run/display from a button etc ?

    Corey....



  2. #2
    Bob Phillips
    Guest

    Re: ~~ Help with Userform and Combo Box ~~

    assign a macro to the button, and in the macro,. add

    Userform1.Show

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Corey" <[email protected]> wrote in message
    news:[email protected]...
    > I have no experience with Userforms and combo boxes, but think they will
    > assist me in achieving what i am trying to do.
    >
    > I have formatted a Userform with a combo box, but :
    >
    > How do i activate it from the worksheet ?
    >
    > I generally use the 'Asign macro' to buttons etc. BUT the userform and

    combo
    > box does not show is the list of macro's.
    >
    > How do i asign the userform to run/display from a button etc ?
    >
    > Corey....
    >
    >




  3. #3
    Dave Peterson
    Guest

    Re: ~~ Help with Userform and Combo Box ~~

    Create another macro in a general module:

    Option Explicit
    sub showtheform()
    userform1.show
    end sub

    You may want to look at how Debra Dalgleish approached it:
    http://www.contextures.com/xlUserForm01.html

    Corey wrote:
    >
    > I have no experience with Userforms and combo boxes, but think they will
    > assist me in achieving what i am trying to do.
    >
    > I have formatted a Userform with a combo box, but :
    >
    > How do i activate it from the worksheet ?
    >
    > I generally use the 'Asign macro' to buttons etc. BUT the userform and combo
    > box does not show is the list of macro's.
    >
    > How do i asign the userform to run/display from a button etc ?
    >
    > Corey....


    --

    Dave Peterson

  4. #4
    Corey
    Guest

    Re: ~~ Help with Userform and Combo Box ~~



    Thanks guy's did the job.
    Having problems with setting the Combo Box to look at the Row Source but
    will persist a bit before aksing for help.
    Regards

    Corey....

    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    > assign a macro to the button, and in the macro,. add
    >
    > Userform1.Show
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "Corey" <[email protected]> wrote in message
    > news:[email protected]...
    >> I have no experience with Userforms and combo boxes, but think they will
    >> assist me in achieving what i am trying to do.
    >>
    >> I have formatted a Userform with a combo box, but :
    >>
    >> How do i activate it from the worksheet ?
    >>
    >> I generally use the 'Asign macro' to buttons etc. BUT the userform and

    > combo
    >> box does not show is the list of macro's.
    >>
    >> How do i asign the userform to run/display from a button etc ?
    >>
    >> Corey....
    >>
    >>

    >
    >




  5. #5
    Corey
    Guest

    Re: ~~ Help with Userform and Combo Box ~~

    Ok.
    I have managed to get thus far.
    Previously i had a button to run (macro11), this triggered other macro's
    (macro2 & macro20) depending
    on the amount of sheets in the workbook.

    If (macro2) was the option, then an Input box would prompt the user for a
    customer name, for a New Sheet.

    But i then realised i wanted a LIST of customer names to choose from, to
    eliminate a single cuatomer being
    entered multiple times as different spellings or variations in names.

    So after a previous posting, i was recommended to use a Userform/Combo Box.
    So below between the double lines in the Code for the Userform and Combo
    Box, that i want to LIST the
    customer names from a range in sheet name"1" in my workbook.
    Then below that i have the relative macro codes that i am using.
    It is the (macro2) that i am needing to be modified to remove the OLD
    customer name prompt, and replace
    this with the Userform and Combo Box instead, but STILL have the New Sheet
    name Indexed as (macro2) does.

    At present the Userform/Combo Box works fine, but i then after selecting the
    customer name, get the OLD
    prompt from (macro2).
    I tried to remove the Input Box steps, but then the New Sheet names did not
    work correctly??

    Any help is appreciated.

    I use a button to run Macro66:
    ==============================================================
    Sub Macro66()
    ' Shows the Userform number below
    ' Macro recorded 29/06/2006 by Corey

    UserForm1.Show
    Call Macro11

    End Sub

    --------------------------------------------------------------------------------------------
    Private Sub ComboBox1_Change()

    End Sub
    --------------------------------------------------------------------------------------------
    Private Sub CommandButton1_Click()
    ' Call Macro11
    ' = UserForm1.ComboBox1.Value
    UserForm1.Hide
    ' ReviewItem
    End Sub
    --------------------------------------------------------------------------------------------
    Private Sub CommandButton2_Click()
    UserForm1.Hide
    End Sub

    Private Sub UserForm_Click()

    End Sub
    ==============================================================

    Sub Macro11()
    ' This macro runs (1) macro if number of sheets is = to (x), or if < (x)
    then runs another macro
    With ActiveWorkbook
    If .Worksheets.Count = 10 Then
    Call Macro20
    Exit Sub
    End If
    If .Worksheets.Count < 10 Then
    Call Macro2

    End If
    End With
    End Sub

    -------------------------------------------------------------------------------------------------

    Sub Macro20()
    ' This macro prompts you that MAX sheets is reached and to Name the New Copy
    (sheet1,sheet2)
    ' Range("A4").Select
    ' Selection.ClearContents
    res = InputBox("MAXIMUM File SIZE REACHED, What do you want to NAME the NEW
    file ? ", "Company Name here...")
    If res = "" Then Exit Sub
    ThisWorkbook.Save
    ' ThisWorkbook.Close
    With ActiveWorkbook
    Worksheets(Array("Enter-Exit Page", "1")).Copy
    ' Application.Dialogs(xlDialogSaveAs) = (res) ' <==== Want to SAVE the
    NEW Copy of the WorkBook as the Value placed in the Input Box ????
    End With
    ActiveWindow.DisplayWorkbookTabs = True
    With ActiveWindow
    .DisplayHorizontalScrollBar = True
    .DisplayVerticalScrollBar = False
    End With
    End Sub

    ----------------------------------------------------------------------------------------

    Sub Macro2()
    ' Created by Corey on 28/6/2006.... with assistance of course
    ' This macro prompts for a Customer Name, and calls the new sheet the NEXT
    number
    Dim sh As Worksheet
    Dim msg As String, sName As String
    Dim res As String
    res = InputBox("What Is the Customer Name ?", "Company Name here....")
    If res = "" Then Exit Sub
    Do
    sName = ActiveWorkbook.Worksheets.Count
    If sName = "" Then Exit Sub
    On Error Resume Next
    Set sh = Worksheets(sName)
    On Error GoTo 0
    msg = "Nothing here "
    Loop While Not sh Is Nothing
    With ActiveWorkbook
    .Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
    End With
    ActiveSheet.Name = sName
    [B3].Select
    ActiveCell.Value = res
    End Sub

    --------------------------------------------------------------------------------------
    Corey....



  6. #6
    Dave Peterson
    Guest

    Re: ~~ Help with Userform and Combo Box ~~

    I had trouble following your code and since it's not too long, I think I'd just
    embed the procedures directly into the userform module--I wouldn't have used
    "Call macroxx" in this case (but it's a personal preference).

    Anyway, I also got rid of some of the code that did non-essential stuff. You
    can add that back when you get the essential stuff working (saving this
    workbook, displaying tabs and scrollbars).

    But this may give you more stuff to play with (or not). All this goes behind
    the userform:

    Option Explicit
    Private Sub ComboBox1_Change()
    Call CheckToEnableOkButton
    End Sub
    Private Sub CommandButton1_Click()

    Dim myFileName As Variant
    Dim NewWksName As String
    Dim NewWkbk As Workbook
    Dim NewWks As Worksheet

    NewWksName = Me.ComboBox1.Value

    With ActiveWorkbook
    If .Worksheets.Count >= 10 Then
    myFileName = Application.GetSaveAsFilename _
    (filefilter:="Excel Files, *.xls", _
    Title:="MAXIMUM File SIZE REACHED, " _
    & "What do you want to NAME the NEW file")

    If myFileName = False Then
    MsgBox "Your file was not saved!!!!" _
    & vbLf & "Process cancelled!"
    Exit Sub
    End If
    .Worksheets(Array("Enter-Exit Page", "1")).Copy
    Set NewWkbk = ActiveWorkbook
    Else
    Set NewWkbk = ActiveWorkbook
    End If
    End With

    With NewWkbk 'either the new workbook or the activeworkbook
    Set NewWks = Nothing
    On Error Resume Next
    Set NewWks = .Worksheets(NewWksName)
    On Error GoTo 0
    If NewWks Is Nothing Then
    'doesn't already exist
    'keep going
    .Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
    Set NewWks = ActiveSheet
    With NewWks
    .Name = Me.ComboBox1.Value
    .Range("B3").Value = NewWksName
    End With
    'save the new workbook here
    Application.DisplayAlerts = False
    .SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
    Application.DisplayAlerts = True
    Else
    MsgBox "Worksheet named: " & NewWksName & " already exists." _
    & vbLf & "Try again"
    End If
    End With
    End Sub

    Private Sub CommandButton2_Click()
    Unload Me
    End Sub

    Private Sub UserForm_Initialize()
    With ThisWorkbook.Worksheets("Sheet1")
    Me.ComboBox1.List _
    = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Value
    End With
    Me.ComboBox1.Style = fmStyleDropDownList
    With Me.CommandButton1
    .Enabled = False
    .Caption = "Ok"
    End With

    With Me.CommandButton2
    .Enabled = True
    .Caption = "Cancel"
    End With

    End Sub
    Private Sub CheckToEnableOkButton()
    Me.CommandButton1.Enabled = False
    If Me.ComboBox1.ListIndex >= 0 Then
    Me.CommandButton1.Enabled = True
    End If
    End Sub

    Please test the heck out of it. I wasn't quite sure what you were doing so I
    could have screwed it up really bad!


    Corey wrote:
    >
    > Ok.
    > I have managed to get thus far.
    > Previously i had a button to run (macro11), this triggered other macro's
    > (macro2 & macro20) depending
    > on the amount of sheets in the workbook.
    >
    > If (macro2) was the option, then an Input box would prompt the user for a
    > customer name, for a New Sheet.
    >
    > But i then realised i wanted a LIST of customer names to choose from, to
    > eliminate a single cuatomer being
    > entered multiple times as different spellings or variations in names.
    >
    > So after a previous posting, i was recommended to use a Userform/Combo Box.
    > So below between the double lines in the Code for the Userform and Combo
    > Box, that i want to LIST the
    > customer names from a range in sheet name"1" in my workbook.
    > Then below that i have the relative macro codes that i am using.
    > It is the (macro2) that i am needing to be modified to remove the OLD
    > customer name prompt, and replace
    > this with the Userform and Combo Box instead, but STILL have the New Sheet
    > name Indexed as (macro2) does.
    >
    > At present the Userform/Combo Box works fine, but i then after selecting the
    > customer name, get the OLD
    > prompt from (macro2).
    > I tried to remove the Input Box steps, but then the New Sheet names did not
    > work correctly??
    >
    > Any help is appreciated.
    >
    > I use a button to run Macro66:
    > ==============================================================
    > Sub Macro66()
    > ' Shows the Userform number below
    > ' Macro recorded 29/06/2006 by Corey
    >
    > UserForm1.Show
    > Call Macro11
    >
    > End Sub
    >
    > --------------------------------------------------------------------------------------------
    > Private Sub ComboBox1_Change()
    >
    > End Sub
    > --------------------------------------------------------------------------------------------
    > Private Sub CommandButton1_Click()
    > ' Call Macro11
    > ' = UserForm1.ComboBox1.Value
    > UserForm1.Hide
    > ' ReviewItem
    > End Sub
    > --------------------------------------------------------------------------------------------
    > Private Sub CommandButton2_Click()
    > UserForm1.Hide
    > End Sub
    >
    > Private Sub UserForm_Click()
    >
    > End Sub
    > ==============================================================
    >
    > Sub Macro11()
    > ' This macro runs (1) macro if number of sheets is = to (x), or if < (x)
    > then runs another macro
    > With ActiveWorkbook
    > If .Worksheets.Count = 10 Then
    > Call Macro20
    > Exit Sub
    > End If
    > If .Worksheets.Count < 10 Then
    > Call Macro2
    >
    > End If
    > End With
    > End Sub
    >
    > -------------------------------------------------------------------------------------------------
    >
    > Sub Macro20()
    > ' This macro prompts you that MAX sheets is reached and to Name the New Copy
    > (sheet1,sheet2)
    > ' Range("A4").Select
    > ' Selection.ClearContents
    > res = InputBox("MAXIMUM File SIZE REACHED, What do you want to NAME the NEW
    > file ? ", "Company Name here...")
    > If res = "" Then Exit Sub
    > ThisWorkbook.Save
    > ' ThisWorkbook.Close
    > With ActiveWorkbook
    > Worksheets(Array("Enter-Exit Page", "1")).Copy
    > ' Application.Dialogs(xlDialogSaveAs) = (res) ' <==== Want to SAVE the
    > NEW Copy of the WorkBook as the Value placed in the Input Box ????
    > End With
    > ActiveWindow.DisplayWorkbookTabs = True
    > With ActiveWindow
    > .DisplayHorizontalScrollBar = True
    > .DisplayVerticalScrollBar = False
    > End With
    > End Sub
    >
    > ----------------------------------------------------------------------------------------
    >
    > Sub Macro2()
    > ' Created by Corey on 28/6/2006.... with assistance of course
    > ' This macro prompts for a Customer Name, and calls the new sheet the NEXT
    > number
    > Dim sh As Worksheet
    > Dim msg As String, sName As String
    > Dim res As String
    > res = InputBox("What Is the Customer Name ?", "Company Name here....")
    > If res = "" Then Exit Sub
    > Do
    > sName = ActiveWorkbook.Worksheets.Count
    > If sName = "" Then Exit Sub
    > On Error Resume Next
    > Set sh = Worksheets(sName)
    > On Error GoTo 0
    > msg = "Nothing here "
    > Loop While Not sh Is Nothing
    > With ActiveWorkbook
    > .Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
    > End With
    > ActiveSheet.Name = sName
    > [B3].Select
    > ActiveCell.Value = res
    > End Sub
    >
    > --------------------------------------------------------------------------------------
    > Corey....


    --

    Dave Peterson

  7. #7
    Corey
    Guest

    Re: ~~ Help with Userform and Combo Box ~~

    Thanks for the replay Dave,

    I have input the code, but get an error at the <== line :



    With NewWkbk 'either the new workbook or the activeworkbook
    Set NewWks = Nothing
    On Error Resume Next
    Set NewWks = .Worksheets(NewWksName)
    On Error GoTo 0
    If NewWks Is Nothing Then
    'doesn't already exist
    'keep going
    .Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
    Set NewWks = ActiveSheet
    With NewWks
    .Name = Me.ComboBox1.Value
    .Range("B3").Value = NewWksName
    End With
    'save the new workbook here
    Application.DisplayAlerts = False
    .SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
    <================= ERROR ===========
    Application.DisplayAlerts = True
    Else
    MsgBox "Worksheet named: " & NewWksName & " already exists." _
    & vbLf & "Try again"
    End If
    End With
    End Sub

    I think this is where the New WorkBook should be Named.
    THIS is ONLY Needed when the Maximum Number of sheets has reached 10.
    ELSE a New WorkSheet is created in the active workbook.

    Corey....




  8. #8
    Corey
    Guest

    Re: ~~ Help with Userform and Combo Box ~~

    Dave,
    Just realised that the Userform/Combo Box actually names the SHEET the value
    from the Combo Box.

    This Value is to be Pasted into the New Sheet cell [B3], and the New Sheet
    is Named the Next Indexed Value. (1,2,3,4,5.... etc)



    Corey....



  9. #9
    Dave Peterson
    Guest

    Re: ~~ Help with Userform and Combo Box ~~

    This is the portion that names the new sheet:

    With NewWks
    .Name = Me.ComboBox1.Value '<--- this line does the rename
    .Range("B3").Value = NewWksName
    End With

    But I'm not sure what the error is on that other line.

    Any chance you're saving to a location that you don't have write access to?

    Corey wrote:
    >
    > Thanks for the replay Dave,
    >
    > I have input the code, but get an error at the <== line :
    >
    > With NewWkbk 'either the new workbook or the activeworkbook
    > Set NewWks = Nothing
    > On Error Resume Next
    > Set NewWks = .Worksheets(NewWksName)
    > On Error GoTo 0
    > If NewWks Is Nothing Then
    > 'doesn't already exist
    > 'keep going
    > .Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
    > Set NewWks = ActiveSheet
    > With NewWks
    > .Name = Me.ComboBox1.Value
    > .Range("B3").Value = NewWksName
    > End With
    > 'save the new workbook here
    > Application.DisplayAlerts = False
    > .SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
    > <================= ERROR ===========
    > Application.DisplayAlerts = True
    > Else
    > MsgBox "Worksheet named: " & NewWksName & " already exists." _
    > & vbLf & "Try again"
    > End If
    > End With
    > End Sub
    >
    > I think this is where the New WorkBook should be Named.
    > THIS is ONLY Needed when the Maximum Number of sheets has reached 10.
    > ELSE a New WorkSheet is created in the active workbook.
    >
    > Corey....


    --

    Dave Peterson

  10. #10
    Corey
    Guest

    Re: ~~ Help with Userform and Combo Box ~~

    Thanks again Dave.

    I have the UserForm and Combo Box working Excatly as required now, except
    for
    1 thing.
    IS THERE A line i can ADD to CLEAR the Combo Box from the Last Entry?

    Corey....



  11. #11
    Bob Phillips
    Guest

    Re: ~~ Help with Userform and Combo Box ~~

    Combobox1.ListIndex = -1

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Corey" <[email protected]> wrote in message
    news:%[email protected]...
    > Thanks again Dave.
    >
    > I have the UserForm and Combo Box working Excatly as required now, except
    > for
    > 1 thing.
    > IS THERE A line i can ADD to CLEAR the Combo Box from the Last Entry?
    >
    > Corey....
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1