How do I save a value from a combobox on a userform to my sheet where ......

[keithaul]

I have a userform that has a combobox control on it. The rowsource property is set to a named range

My data source sheet has a column that is set up with a data validation where the criteria is set as a list and the source is the same named range as the combobox.

I have an update button on the userform for the purpose of updating my datasource sheet with the values from the user form

However when I want to update the field on the sheet that has the data validation on it, the value from my combobox does not get transferred to the sheet. Below is the code Im using:
Cells(currentrow, 2).Value = Me.cboCatg.Text

Why isn't the cell updating and how can I transfer the value I select from the combobox on the userform to the cell that has the data validation set?

I'm using similar code for the other form fields and my datasource sheet gets update with the values. However the other cells do not have the data validation set to a named range.

Combobox Value Not Transferring to sheet on update.jpg

[SamT]

The first thing I would try is turning events off

Application.EnableEvents = False
Cells(currentrow, 2).Value = Me.cboCatg.Text
Application.EnableEvents = True

OTOH, if all Data Entry is by UserForm, don't use Data Validation.

Tip: Looking at that screenshot, I imagine your Data Transfer code is quite long. If you place the appropriate column Letter in each Controls Tag property, then

Dim Ctrl As Object
For Each Ctrl in Me.Controls
   If Ctrl.Tag <> "" Then Cells(CurrentRow, Ctrl.Tag) = Ctrl
Next Ctrl

If you use Column Numbers in the Tag then you can treat different controls differently
IF Ctrl.Tag > 0 and < 1000 then Cells(CurrentRow, Tag)
ElseIf Ctrl.Tag > 1000 And < 10000 then Cells(RowNum, Tag - 1000)

If you imagine you might ever change the structure of your project you can use a function to return the Column letter or number.

Private Function ColAlpha(Ctrl as Object) As String
Select Case Ctrl.Name
Case "cboCatg": ColAlpha = "B"
Case "CommandButton1": ColAlpha = "C" 'Silly, eh?
End Select
End Function

Dim Ctrl As Object
For Each Ctrl in Me.Controls
    Cells(CurrentRow, ColAlpha(Ctrl)) = Ctrl
Next Ctrl

Yeah, I'm a fan of using Letters in Cells(Row, Column)

[keithaul]

Thank you for your reply. However this is what happened when I added the Application.EnableEvents code

I have 2 worksheets in this workbook. If I select the worksheet that I'm not updating, and then run the UserForm by selecting 'Run' from the VBE and then 'Run Sub/Userform', the Datavalidation cell does get updated with the value I select from the combobox in the userform.
If I select the worksheet I am updating and then run the userform from within the VBE, the data validation cell does not get updated. Below is the code I added:

Application.EnableEvents = False
Worksheets("Sold Items").Cells(currentrow, 2).Value = cboCatg.Text
Application.EnableEvents = True

As for your other suggestions, yes I have a large worksheet with 22 columns to update. I've never used the Controls Tag property before because I was not aware of it. So I have to learn this, so I can implement your suggestion

Keith

[snb]

If you use 'rowsource' you can't change the rowsource with the combobox, because 'rowsource' is read-only.

[SamT]

I'm beginning to doubt that DV is an issue. However, do you really need DV?

Do you have the words Activate, ActiveSheet, ActiveCell, Select, or Selection anywhere in your code?

The Tag is one of the Properties you can set during Design Phase.