Pass Row and Column Number Index To Refer And Put Value To Cell Range

[Excel Fox]

This is how you can pick the row and column from the caption of the button, and from the selection in combobox, and then reference that cell.

Private Sub CommandButton1_Click()
     Dim lngRow as Long, lngCol as Long
     lngRow = buildgPlan.CommandButton1.Caption'Get Row
     lngCol = Me.ComboBoxDeterminingColumn.Value
    .Cells(lngRow, lngCol).Value = Me.ComboBox1.Value    
    End Sub

[bakerman]

Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 0 To 2
        .Cells(buildgPlan.CommandButton1.Caption, Me.ComboBoxDeterminingColumn.Value + i).Value = Me("ComboBox" & i + 1).Value
     Next
End Sub

[wscottd]

thanks for the input,

When I run it an error comes back as "Compile Error: Method or data member not found for "Me.ComboBoxDeterminingColumn.Value"

I tried a couple of codes in ComboBox1 to state the column but could not get it to work.

[Excel Fox]

Scott, that is supposed to be the name of the combobox you want to use. I was just using some name for you to refer to.

[wscottd]

1Assessment Form.xlsmThank you,

I tried placing ComboBox1.value in it's place but still received an error. I guess I thought a reference would be needed under each of the codes for the ComboBox to be able to write multiple comboboxes to the designated columns.

I have attached the project to help explain what I'm trying to do. If you run the forms from the beginning "aOpeningSht" and select the boxes through to the end it should explain more clearly.

Thanks again
Scott

[bakerman]

Why 5 Userforms (and more to come) ? This can all be done with one single userform.
This way you are not headed towards a programmatic disaster when everything is up and running.
One form, 3 CB on top for Campus - Building - Room and 3 frames or multipages for Walls - Floor - Ceiling and you're done.

[wscottd]

Thanks Bakerman & ExcelFox Admin

I agree, and the consensus is, the first run at this is definitely not the way to go and will make the project overly complex. I will go back and restructure the project.

Thanks again for the advice, it is quite helpful for getting things started in the correct direction.