CODEWITHSUNDEEP
vba
MilesToGoBeforeISleep
MilesToGoBeforeISleep

Reputation: 183

Using User Form to update specific cells

I created a user form with two combo boxes ("cmb1" and "cmb2"), each referring to a range of values. I need to:

1) Permanently show this user form on a specific worksheet (let's call with "ws")

2) Update values of ws.Cells(B,4) to match the value chosen by user on cmb1 and similarly, ws.Cells(A,2) to the value chosen by user on cmb2.

I have created a CommandButton ("Enter")on the user form as well. The updates need to happen once that button is clicked.

My code is giving me a run-time error on me.cmb1

Private Sub Enter_Click()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("ws")

ws.Cells(B, 4) = Me.cmb1
ws.Cells(A, 2) = Me.cmb2
End Sub

Please help. Thanks!

Upvotes: 0

Views: 68

Answers (1)

Ilan
Ilan

Reputation: 28

You're using .Cells which requires a number for both the row and column reference. The run-time error is being triggered because you are using a letter for your column reference.

You have two options to save the values from the comboboxes (probably more, but two simple ways).

Option 1 - .Cells (note that it is row first, then column)

ws.Cells(4,2) = Me.cmb1.Value
ws.Cells(2,1) = Me.cmb.value

Option 2 - .Range

ws.Range("B4") = Me.cmb1.Value
ws.Cells("A2") = Me.cmb.value

See http://excelmatters.com/referring-to-ranges-in-vba/ for more info

Upvotes: 1

Related Questions