UserForm: Textbox value load according to drop down list

[jc2015]

Hi gurus,


I have a userform that I need it to load the value in the textbox according to the dropdown list


For example:
If outlet ABC selected, it 3 textbox need to show the value in same row of ABC outlet.


Beside that, there is two button I need it to function as well


Save button:


Save the textbox value according to the drop down list selected


For example, ABC outlet selected and there if there is a changes of any 3 textbox, say Promoter name change from David to Dave the save button need to save according to the cell selected


Delete button:


Need to delete whole row of selected outlet

i found this more or less the same situation from:


Excel VBA code for textbox value dependent on dropdown list value using named ranges - Stack Overflow


and I modify it to suit my current condition:

Private Sub CommandButton2_Click()


Unload Me


End Sub
Private Sub UserForm_Initialize()


Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim WS4 As Worksheet
Dim WS5 As Worksheet
Dim WS6 As Worksheet


Set WS1 = Worksheets("MonthSummary")
Set WS2 = Worksheets("WeekSummary")
Set WS3 = Worksheets("DatabaseForm")
Set WS4 = Worksheets("DatabasePO")
Set WS5 = Worksheets("Setup")
Set WS6 = Worksheets("Products")


Dim range_a As Range


    For Each range_a In Worksheets("Setup").Range("OutletList")
      With Me.cmbOutlet
        .AddItem range_a.Value
        .List(.ListCount - 1, 1) = range_a.Offset(0, 1).Value
      End With
    Next range_a


lbName = WS5.Cells(1, 2)
lbSales = WS5.Cells(1, 3)
lbCost = WS5.Cells(1, 4)


End Sub


Private Sub Data_Change()
    With Me.cmbOutlet
        If .ListIndex = -1 Then
            Me.tbName.Text = ""
            Me.tbSales.Text = ""
            Me.tbCost.Text = ""
        Else
            tbName.Text = .List(.ListIndex, 1)
            tbSales.Text = .List(.ListIndex, 2)
            tbCost.Text = .List(.ListIndex, 3)
        End If
    End With
End Sub

but it seems doesnt work with my multiple textbox


May I know how to achieve this?

The Form View:

The sheet view:

Thank you.

 

[Worf]

Hi

Please test this:

Option Explicit
Dim WS5 As Worksheet, WS6 As Worksheet, range_a As Range


Private Sub cmbOutlet_Change()
Data_Change
End Sub


Private Sub CommandButton2_Click()  ' delete sheet row
WS5.Rows(Me.cmbOutlet.ListIndex + 1).Delete
End Sub


Private Sub CommandButton3_Click()
Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet, WS4 As Worksheet
'Set WS1 = Worksheets("MonthSummary"): Set WS2 = Worksheets("WeekSummary")
'Set WS3 = Worksheets("DatabaseForm"): Set WS4 = Worksheets("DatabasePO")
Set WS5 = Worksheets("Setup"): Set WS6 = Worksheets("Products")
Me.cmbOutlet.ColumnCount = 4
For Each range_a In WS5.Range("OutletList")
  With Me.cmbOutlet
    .AddItem range_a.Value
    .List(.ListCount - 1, 1) = range_a.Offset(, 1)
    .List(.ListCount - 1, 2) = range_a.Offset(, 2)
    .List(.ListCount - 1, 3) = range_a.Offset(, 3)
  End With
Next
lbName = WS5.Cells(1, 2)
lbSales = WS5.Cells(1, 3)
lbCost = WS5.Cells(1, 4)
End Sub


Private Sub Data_Change()
With Me.cmbOutlet
    If .ListIndex = -1 Then
        Me.tbName.Text = ""
        Me.tbSales.Text = ""
        Me.tbCost.Text = ""
    Else
        tbName.Text = .List(.ListIndex, 1)
        tbSales.Text = .List(.ListIndex, 2)
        tbCost.Text = .List(.ListIndex, 3)
    End If
End With
End Sub


Private Sub CommandButton1_Click()  ' save to sheet
Dim rn%
rn = Me.cmbOutlet.ListIndex + 1
WS5.Cells(rn, 2) = tbName.Text
WS5.Cells(rn, 3) = tbSales.Text
WS5.Cells(rn, 4) = tbCost.Text
End Sub

 

[jc2015]

Hi

Please test this:

Option Explicit
Dim WS5 As Worksheet, WS6 As Worksheet, range_a As Range


Private Sub cmbOutlet_Change()
Data_Change
End Sub


Private Sub CommandButton2_Click()  ' delete sheet row
WS5.Rows(Me.cmbOutlet.ListIndex + 1).Delete
End Sub


Private Sub CommandButton3_Click()
Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet, WS4 As Worksheet
'Set WS1 = Worksheets("MonthSummary"): Set WS2 = Worksheets("WeekSummary")
'Set WS3 = Worksheets("DatabaseForm"): Set WS4 = Worksheets("DatabasePO")
Set WS5 = Worksheets("Setup"): Set WS6 = Worksheets("Products")
Me.cmbOutlet.ColumnCount = 4
For Each range_a In WS5.Range("OutletList")
  With Me.cmbOutlet
    .AddItem range_a.Value
    .List(.ListCount - 1, 1) = range_a.Offset(, 1)
    .List(.ListCount - 1, 2) = range_a.Offset(, 2)
    .List(.ListCount - 1, 3) = range_a.Offset(, 3)
  End With
Next
lbName = WS5.Cells(1, 2)
lbSales = WS5.Cells(1, 3)
lbCost = WS5.Cells(1, 4)
End Sub


Private Sub Data_Change()
With Me.cmbOutlet
    If .ListIndex = -1 Then
        Me.tbName.Text = ""
        Me.tbSales.Text = ""
        Me.tbCost.Text = ""
    Else
        tbName.Text = .List(.ListIndex, 1)
        tbSales.Text = .List(.ListIndex, 2)
        tbCost.Text = .List(.ListIndex, 3)
    End If
End With
End Sub


Private Sub CommandButton1_Click()  ' save to sheet
Dim rn%
rn = Me.cmbOutlet.ListIndex + 1
WS5.Cells(rn, 2) = tbName.Text
WS5.Cells(rn, 3) = tbSales.Text
WS5.Cells(rn, 4) = tbCost.Text
End Sub

everything working fine except delete button.

the data is save in pivot table with the name "Setup"

The code can delete the 2nd row and below however the first option it couldn't delete...

please advise

 

[Worf]

I don’t think you can delete a pivot table row. Please run the macro below to check if you have a pivot table on that sheet:

Sub CountPvt_Tbl()
Dim sh As Worksheet
Set sh = Sheets("Summary")  ' change name here
MsgBox "# Pivots: " & sh.PivotTables.Count & vbLf & "# Tables: " _
& sh.ListObjects.Count, 64, "Table counting"
End Sub