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

[wscottd]

I have received a lot of help from this site through searching posts but can’t seem to figure out the answer to my problem so I thought I would ask my first questions after several days of trying to solve this on my own.

I’m trying to specify a row when the user chooses a specific command button and then reference the column from a ComboBox which would determine the cell to write to. Then write the contents of the ComboBox to the specific cell.

I can select the column and the row separately but can’t figure out the code to make them work together.

This works to choose the desired row

      Private Sub CommandButton1_Click()
      Dim y As Integer
      y = bBldgPlan.CommandButton1.Caption  ‘(i.e. caption is 100, row 100 is selected)
      Rows(y).Select

This works to write the contents of the forms ComboBox to the cell but requires both column and row to be referenced. I only want the column to be referenced from the ComboBox. Writing a code that performs both the above and below functions is where I am having difficulty.

Private Sub CommandButton1_Click()
    .Range("A6") = Me.ComboBox1.Value
    .Range("A7") = Me.ComboBox2.Value
    .Range("A8") = Me.ComboBox3.Value    
     End With
     
End Sub

Although not even close, this should give an idea of what I’m trying to do.

Private Sub CommandButton1_Click()
     Dim y As Integer
     y=buildgPlan.CommandButton1.Caption          
    .Range(“A”) & y =Me.ComboBox1.Value
    .Range("B") & y = Me.ComboBox2.Value
    .Range("C") & y = Me.ComboBox3.Value    
    End Sub

Any help would be greatly appreciated

[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.