Combo box to select column

**Duguid1** · 08-13-2008, 05:31 AM

Hi all,

I have the code below that allows me to enter data into 3 columns of the next empty row dependant on what is added into text box's on a userform.

However, I require a combo box to be added. This combo box will have 4 choices. e.g. word1,word2,word3,word4.

What I require is if Word 1 is selected then the values in the text box's are added into column 1, If word2 is selcted then the values in the text box's are added to column 5, If word 3, column 9 and if word4 then column 14.

My VBA knowledge doesnt stretch this far....

Private Sub cboRegion_Change()
 'Code needs to be entered to allow entry of words e.g Word1, word2,word3.
End Sub

'This enters the data entered into text box's txtBuyer,txtNumberOfOrders,txtSlippage into the next empty row in column 1

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet


iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

If Trim(Me.txtBuyer.Value) = "" Then
  Me.txtBuyer.SetFocus
  MsgBox "Please enter buyer name"
  Exit Sub
End If

ws.Cells(iRow, 1).Value = Me.txtBuyer.Value
ws.Cells(iRow, 2).Value = Me.TxtNumberOfOrders.Value
ws.Cells(iRow, 3).Value = Me.txtSlippage.Value


Me.txtBuyer.Value = ""
Me.TxtNumberOfOrders.Value = ""
Me.txtSlippage.Value = ""
Me.txtBuyer.SetFocus
End Sub
'This closes the userform
Private Sub cmdClose_Click()
  Unload Me
End Sub

**StephenR** · 08-13-2008, 05:59 AM

Could you perhaps attach a small sample workbook? I take it your use of "word1" etc is merely illustrative?

**royUK** · 08-13-2008, 06:09 AM

Try this

Populate the Combo

Private Sub UserForm_Initialize()
'populate Combo
    Me.cboRegion.List = Array("Word1", "Word2", "Word3", "Word4")
End Sub

Add the data to the sheet

Private Sub cmdAdd_Click()
    Dim C      As Long
    Dim iRow   As Long
    Dim ws     As Worksheet
    Set ws = ActiveSheet
    With Me
        C = .cboRegion.ListIndex + 1
        If C = 0 Then
            MsgBox "Please select a Region", vbCritical, "Input Error"
            .cboRegion.SetFocus
            Exit Sub
        End If
        iRow = ws.Cells(Rows.Count, 1) _
               .End(xlUp).Offset(1, 0).Row

        If Trim(.txtBuyer.Value) = "" Then
            .txtBuyer.SetFocus
            MsgBox "Please enter buyer name"
            Exit Sub
        End If
        With ws
            ws.Cells(iRow, 1).Value = .txtBuyer.Value
            ws.Cells(iRow, 2).Value = .TxtNumberOfOrders.Value
            ws.Cells(iRow, 3).Value = .txtSlippage.Value
            ws.Cells(iRow, C).Value = .cboRegion.Value

            .txtBuyer.Value = ""
            .TxtNumberOfOrders.Value = ""
            .txtSlippage.Value = ""
            .txtBuyer.SetFocus
        End With
    End Sub

I can't see what you mean by adding the TextBoxes to one column. Your code doesnot appear to add the input to one Column.

**Duguid1** · 08-13-2008, 06:25 AM

Hi, I managed to figure that out in the end, using the code below

Private Sub cboRegion_Change()
 cboRegion.AddItem "NEWT"
 cboRegion.AddItem "NWWT"
 cboRegion.AddItem "CDT"
 cboRegion.AddItem "AMB"
 'Code needs to be entered to allow entry of words e.g Word1, word2,word3.
End Sub

'This enters the data entered into text box's txtBuyer,txtNumberOfOrders,txtSlippage into the next empty row in column 1

Private Sub cmdAdd_Click()
Dim iRow As Long

Dim ws As Worksheet
Set ws = ActiveSheet

If cboRegion.Value = "NEWT" Then

    iRow = ws.Cells(Rows.Count, 9) _
    .End(xlUp).Offset(1, 0).Row

        If Trim(Me.txtBuyer.Value) = "" Then
        Me.txtBuyer.SetFocus
        MsgBox "Please enter buyer name"
        Exit Sub
    End If

    ws.Cells(iRow, 9).Value = Me.txtBuyer.Value
    ws.Cells(iRow, 10).Value = Me.TxtNumberOfOrders.Value
    ws.Cells(iRow, 11).Value = Me.txtSlippage.Value


    Me.txtBuyer.Value = ""
    Me.TxtNumberOfOrders.Value = ""
    Me.txtSlippage.Value = ""
    Me.txtBuyer.SetFocus

    Else
        If cboRegion.Value = "NWWT" Then
        iRow = ws.Cells(Rows.Count, 13) _
        .End(xlUp).Offset(1, 0).Row

            If Trim(Me.txtBuyer.Value) = "" Then
            Me.txtBuyer.SetFocus
            MsgBox "Please enter buyer name"
            Exit Sub
            End If

        ws.Cells(iRow, 13).Value = Me.txtBuyer.Value
        ws.Cells(iRow, 14).Value = Me.TxtNumberOfOrders.Value
        ws.Cells(iRow, 15).Value = Me.txtSlippage.Value


        Me.txtBuyer.Value = ""
        Me.TxtNumberOfOrders.Value = ""
        Me.txtSlippage.Value = ""
        Me.txtBuyer.SetFocus


Else
If cboRegion.Value = "CDT" Then
    iRow = ws.Cells(Rows.Count, 5) _
    .End(xlUp).Offset(1, 0).Row

        If Trim(Me.txtBuyer.Value) = "" Then
        Me.txtBuyer.SetFocus
        MsgBox "Please enter buyer name"
        Exit Sub
        End If

ws.Cells(iRow, 5).Value = Me.txtBuyer.Value
ws.Cells(iRow, 6).Value = Me.TxtNumberOfOrders.Value
ws.Cells(iRow, 7).Value = Me.txtSlippage.Value


Me.txtBuyer.Value = ""
Me.TxtNumberOfOrders.Value = ""
Me.txtSlippage.Value = ""
Me.txtBuyer.SetFocus

Else
    If cboRegion.Value = "AMB" Then
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

        If Trim(Me.txtBuyer.Value) = "" Then
        Me.txtBuyer.SetFocus
        MsgBox "Please enter buyer name"
        Exit Sub
        End If

ws.Cells(iRow, 1).Value = Me.txtBuyer.Value
ws.Cells(iRow, 2).Value = Me.TxtNumberOfOrders.Value
ws.Cells(iRow, 3).Value = Me.txtSlippage.Value


Me.txtBuyer.Value = ""
Me.TxtNumberOfOrders.Value = ""
Me.txtSlippage.Value = ""
Me.txtBuyer.SetFocus

End If
End If
End If
End If




End Sub

Roy, I have added the words to this code in the top few lines ("AMB,NEWT" etc).
However, i have found that this code works, apart from the fact that it doesnt show the available choices in the dropdown menu, it is blank until a letter is entered. Is it possible to show the available choices in the drop down menu before anything is entered?

Regards

**royUK** · 08-13-2008, 06:53 AM

I mprovided code for you that loads the ComboBox using the form's initialize event

Private Sub UserForm_Initialize()
'populate Combo
    Me.cboRegion.List = Array("Word1", "Word2", "Word3", "Word4")
End Sub

And this should enter the data into the correct Columns

'This enters the data entered into text box's txtBuyer,txtNumberOfOrders,txtSlippage into the next empty row in column 1

Private Sub cmdAdd_Click()
    Dim C      As Long
    Dim iRow   As Long
    Dim ws     As Worksheet
    Set ws = ActiveSheet
    
    With Me
        Select Case .cboRegion.ListIndex
            Case 0: C = 9    '"NEWT
            Case 1: C = 13    '"NWWT"
            Case 2: C = 5    '"CDT"
            Case 3: C = 1    '"AMB"
            Case Else
                MsgBox "Please select a Region", vbCritical, "Input Error"
                .cboRegion.SetFocus
                Exit Sub
        End Select
        iRow = ws.Cells(Rows.Count, 1) _
               .End(xlUp).Offset(1, 0).Row

        If Trim(.txtBuyer.Value) = "" Then
            .txtBuyer.SetFocus
            MsgBox "Please enter buyer name"
            Exit Sub
        End If
        
        With ws
            ws.Cells(iRow, C).Value = .txtBuyer.Value
            ws.Cells(iRow, C).Value = .TxtNumberOfOrders.Value
            ws.Cells(iRow, C).Value = .txtSlippage.Value

            .txtBuyer.Value = ""
            .TxtNumberOfOrders.Value = ""
            .txtSlippage.Value = ""
            .txtBuyer.SetFocus
            .cmdAdd.Enabled=False 'disable add button
        End With
    End Sub

Yopu could make sure that the user can only use the add button if a region is selected by disabling the add buton.

Private Sub UserForm_Initialize()
'populate Combo
   with Me
.cboRegion.List = Array("Word1", "Word2", "Word3", "Word4")
.cmdAdd.Enabled = False
End With
End Sub
'enable the add button after selection
Private Sub cboRegion_Change()
 Me.cmdAdd.Enabled=True
End Sub

Thread: Combo box to select column

Thread Tools

Display

Combo box to select column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions