Results 1 to 8 of 8

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

  1. #1
    Junior Member
    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0

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

    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
    Code:
          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.
    Code:
    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.

    Code:
    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
    Last edited by Excel Fox; 04-19-2013 at 11:06 AM. Reason: Code Tags Added

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.

    Code:
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    Code:
    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

  4. #4
    Junior Member
    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0
    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.

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0
    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
    Last edited by wscottd; 04-20-2013 at 06:57 AM.

  7. #7
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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.

  8. #8
    Junior Member
    Join Date
    Apr 2013
    Posts
    4
    Rep Power
    0
    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.

Similar Threads

  1. Replies: 4
    Last Post: 06-01-2013, 01:08 PM
  2. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  3. Highlight Active Cell’s Row and Column
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  4. Lookup From Cell Range By Matching Row and Column
    By paul_pearson in forum Excel Help
    Replies: 2
    Last Post: 03-07-2013, 02:02 PM
  5. Replies: 4
    Last Post: 02-22-2013, 02:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •