Selected Answer
Bjotr
You could just write values to the other sheet directly, using this (changes in bold):- I used it for the UserForm_Click event:
Private Sub UserForm_Click()
Dim CellAdd As String
Rw = ActiveCell.Row
For x = 1001 To 1198
CellAdd = Cells(Rw, x).Address
Sheets("Sjekkliste 2022").Range(CellAdd) = frmFasteOpplysninger.Controls("ComboBox" & x).Value
Next x
End Sub
I found it much trickier to set the ControlSource property successfully to a cell on the other sheet but found a way....
In the FIRST attached file, Sheet1 has a button to show a form with just 3 comboboxes. The code behind it is just:
Private Sub CommandButton1_Click()
frmFasteOpplysninger.Show
End Sub
(using your version of userform, frmFasteOpplysninger ).
If you pick a cell in Sheet1 (and note the row number), type values in the three boxes then click the lilac button "Set controlsource/ store values", this code will set the ControlSource per combobox so the values appear in the other sheet. I've added comments for you to follow (and delete if you like)
Private Sub CommandButton1_Click()
Dim CellAdd As String, FirstCB As Long
' store the CB number for start of loop
FirstCB = 1
Rw = ActiveCell.Row
For x = FirstCB To 3
'create address string
CellAdd = Cells(Rw, x).Address
'get CB contents
Temp = frmFasteOpplysninger.Controls("ComboBox" & x).Text
'select this control
frmFasteOpplysninger.Controls("ComboBox" & x).SetFocus
' set the controlsource
frmFasteOpplysninger.Controls("ComboBox" & x).ControlSource = "Sjekkliste_2022!" & CellAdd
' rewrite the value
frmFasteOpplysninger.Controls("ComboBox" & x).Text = Temp
Next x
' select the first control (so last is stored)
frmFasteOpplysninger.Controls("ComboBox" & FirstCB).SetFocus
' show stored values
Worksheets("Sjekkliste_2022").Activate
Cells(Rw, 1).Select
End Sub
It will show the sheet Sjekkliste_2022 (notice NO space in the name) and you can change values in the form and see them change in the sheet. Once you close the form however, the ControlSources reset to the default "".
Note that I found it was necessary to move between comboboxes (using .SetFocus) to get the value into the sheet. Likewise to move to the first CB (after the loop) to get the last value stored.
REVISION:
In the SECOND attached file, I guessed that you wanted to take some existing data and edit that/ store in sheet Sjekkliste_2022 so...
Sheet1 also has 4 rows of data (plus the button to show the form). In this file, the ComboBox values are taken from Sheet1 and the ControlSource set on the sheet Sjekkliste_2022. Pick one of the four data rows in Sheet1 (click Show...) and the row to be editted in that sheet then appears with a yellow fill (which is removed once the "Save..." button is clicked).
The variables are declared outside the individual codes (so useable between them) and you need to change the items in bold below:
Dim CellAdd As String, FirstCB As Long, LastCB As Long, wsOut As Worksheet, Rw As Long
Private Sub CommandButton1_Click()
'remove highlight from row
wsOut.Rows(Rw).Interior.Color = xlNone
MsgBox "Any changes saved to " & wsOut.Name & " (only)"
'close form
Unload Me
End Sub
Private Sub UserForm_Initialize()
' store the CB number for start and end of loop
FirstCB = 1
LastCB = 3
' state destination worksheet
Set wsOut = Worksheets("Sjekkliste_2022")
Rw = ActiveCell.Row
' loop in current sheet
For x = FirstCB To LastCB
'get the value from the current row
Me.Controls("ComboBox" & x).Value = Cells(Rw, x)
Next x
' show stored values
wsOut.Activate
For x = FirstCB To LastCB
'create address string
CellAdd = Cells(Rw, x).Address
'get CB contents
Temp = frmFasteOpplysninger.Controls("ComboBox" & x).Text
'select this control
frmFasteOpplysninger.Controls("ComboBox" & x).SetFocus
' set the controlsource
frmFasteOpplysninger.Controls("ComboBox" & x).ControlSource = "Sjekkliste_2022!" & CellAdd
' rewrite the value
frmFasteOpplysninger.Controls("ComboBox" & x).Text = Temp
Next x
' select the first control (so last is stored)
frmFasteOpplysninger.Controls("ComboBox" & FirstCB).SetFocus
'add highlight to row
wsOut.Rows(Rw).Interior.Color = vbYellow
MsgBox "You can now change values in " & wsOut.Name & vbCr & "(Press enter to change an item)"
End Sub
I think this should work with FirstCB=1001 and For x = FirstCB To To 1198 but I leave you to try.
Hope this helps
