get conditional lastrow value based on Combox Selected Text

Infomedia Plus 0
Viewed 19

here I have serial number in textbox (txt_Rec) from worksheet("Sale") lastrow.

Now i want to get conditional values (Serial Number) in textbox (txt_Rec), if i select "Sale" from combobox(Me.cmb_Type)dropdown list then i want worksheet("Sale") lastrow serial number in textbox (txt_Rec) and if i select "Purchase" from combobox(cmb_Type)dropdown list then i want worksheet("Purchase") lastrow serial number in textbox (txt_Rec).

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sale")
Dim lastrow As Long
lastrow = Application.WorksheetFunction.CountA(sh.Range("A:A"))
Me.txt_Rec.Value = lastrow

``` dropdown for Item type ```

With Me.cmb_Type
.Clear
.AddItem ""
.AddItem "Sale"
.AddItem "Purchase"
End With
End Sub
combobox vba excel
1 Answers

Please, copy the next event code in the form code module:

Private Sub cmb_Type_Change()
      Dim sh As Worksheet
      If cmb_Type.Value <> "" Then
            Set sh = ThisWorkbook.Sheets(cmb_Type.Value)
            Me.txt_Rec.Text = sh.Range("A" & sh.rows.count).End(xlUp).row
      Else
            Me.txt_Rec.Text = ""
     End If
End Sub

And comment the next code lines from the shown Initialize event:

      'Dim lastrow As Long
      'lastrow = Application.WorksheetFunction.CountA(sh.Range("A:A"))
      'Me.txt_Rec.Value = lastrow

If you want the combo to initially show "Sale", you should add

      .ListIndex = 1

after .AddItem "Purchase" and before End With...

FaneDuru0
Related