0

I would like to overwrite data using a Userform, I can call the data to the form based on a Combobox (unique reference from column A in my data sheet). I am failing to send updated data back, and am stuck on a Run-time error '13.

I have looked at a number of posts but cannot pick out a thread to success! Any help appreciated. I have left the code simple, to update the 4 column of that row. Ultimately I will expand from the 2nd column onwards. 

Private Sub cmbtrade_Change() - this part works as expected
Dim trade_name As String

If Me.cmbtrade.Value = "" Then

MsgBox "Trade Can Not be Blank!!!", vbExclamation, "Trade"

Exit Sub

End If

trade_name = cmbtrade.Value

On Error Resume Next

Dim trade As Double
trade_name = cmbtrade.Value
TextBox16.Text = Application.WorksheetFunction.VLookup(trade_name, 
Sheets("Sheet2").Range("A2:D43"), 4, False)

End Sub

The problem part....

Private Sub cmdupdate_Click()
If Me.cmbtrade.Value = "" Then

MsgBox "Trade Name Can Not be Blank", vbExclamation, "Trade"
Exit Sub
End If
trade_name = cmbtrade.Value
Sheets("sheet2").Select
Dim rowselect As Double

rowselect = Me.cmbtrade.Value (this is where my mismatch error occurs)
rowselect = rowselect + 1
Rows(rowselect).Select

Cells(rowselect, 4) = Me.TextBox16.Value
End Sub

enter image description here

mattp
  • 1
  • 1
  • Try declaring `rowselect as long` (integer) and you might have to use convert the combobox value to a number as by default it's text. – SJR Apr 24 '19 at 12:46
  • And read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Apr 24 '19 at 12:49

1 Answers1

0

Try this. You don't actually need to convert the combobox to a Long, but it's good practice I think.

Private Sub cmdupdate_Click()

If Me.cmbtrade.Value = "" Then
    MsgBox "Trade Name Can Not be Blank", vbExclamation, "Trade"
    Exit Sub
End If

Dim rowselect As Long

rowselect = CLng(Me.cmbtrade.Value) + 1
Sheets("sheet2").Cells(rowselect, 4) = Me.TextBox16.Value

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • Thanks for the quick response, on test I am getting the same Run-time error '13 type mismatch on the same line, rowselect = clng... – mattp Apr 25 '19 at 07:44
  • What's the combobox value? – SJR Apr 25 '19 at 09:29
  • the combobox will have alpha/numeric text pulled in from column A – mattp Apr 25 '19 at 12:53
  • Well you can't have an alpha-numeric row number. It has to be a number. – SJR Apr 25 '19 at 12:58
  • OK, I set up the combobox to pull in search criteria that would likely be text, which would then populate the rest of the fields of the form via vlookup, which would be both numeric and text . The idea being that the user would/could edit the fields pulled in and then update the row with any changes - which is the part I cannot get my head around – mattp Apr 25 '19 at 14:25
  • Can you post a screenshot? Are you saying the combobox value will be found on the sheet and you want to reference that row and add Textbox16 to it? – SJR Apr 25 '19 at 14:34
  • The Combo takes from Column A (the top code I posted works on this), the textbox 16 would update column D Supplier g. The goal is that any column from B onward could be updated – mattp Apr 25 '19 at 15:11