none
Help for connecting a userform with a sheet RRS feed

  • Question

  • Hi,

    I have a 50 x 50 blank table in my excelsheet. On the left and upper side of the table are names to compare. I want to use a userform for the comparison. I want to see the row and column names in my userform and than fill the blanks in the table.

    A

    B

    C

    D

    E

    1

    Name1

    Name2

    Name3

    Name4

    2

    Name1

    xxxxxxxx

    3

    Name2

    xxxxxxxx

    4

    Name3

    xxxxxxxx

    5

    Name4

    xxxxxxxx


    So I want my userform to show me Name 1 and Name 2, if I am in C2. Name 2 and Name 1, if I am in B3 and so on. (Of course that will be the double-for-loop, which will go through the cells, not me.)



    • Edited by excelibur Sunday, November 23, 2014 10:51 AM
    • Moved by Carl Cai Monday, November 24, 2014 6:11 AM more related
    Sunday, November 23, 2014 10:50 AM

Answers

  • Add a Userform, add 2 TextBoxes on that form and paste this code into the code module of that form:

    Option Explicit
    Dim WithEvents WS As Worksheet
    
    Private Sub UserForm_Initialize()
      'The textboxes are only for information
      Me.TextBox1.Enabled = False
      Me.TextBox2.Enabled = False
      'Enable our event routines
      Set WS = ActiveSheet
      'Call the event routine manually the first
      WS_SelectionChange ActiveCell
    End Sub
    
    Private Sub WS_SelectionChange(ByVal Target As Range)
      Me.TextBox1 = Cells(1, ActiveCell.Column)
      Me.TextBox2 = Cells(ActiveCell.Row, 2)
    End Sub
    


    Add a regular module and paste this code:

    Sub Main()
      UserForm1.Show vbModeless
    End Sub
    

    Close the VBA editor, run sub Main and select a cell.

    Andreas.

    • Marked as answer by excelibur Monday, November 24, 2014 11:34 PM
    Monday, November 24, 2014 8:15 AM
  • I guess that will be a very easy question, but I couldn't it solve it at the moment
    Well, this is a beginner question, but anything is easy if you know how to do. :-) Study the code, read the help, debug the code, till you understand what is going on.

    BTW, here is a good tutorial:
    http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training.htm

    Andreas.

    Option Explicit
    
    Dim FirstData As Range
    Dim LastColumn As Long, LastRow As Long
    
    Private Sub UserForm_Initialize()
      'Setup the 1st data cell
      Set FirstData = Range("C2")
      'Find the last positions in the row/column headings
      LastColumn = FirstData.Offset(-1).EntireRow.Find("*", SearchDirection:=xlPrevious).Column
      LastRow = FirstData.Offset(, -1).EntireColumn.Find("*", SearchDirection:=xlPrevious).Row
    End Sub
    
    Private Sub CommandButton1_Click()
      'Next button
      If ActiveCell.Column < LastColumn Then
        'Same row, one step right
        ActiveCell.Offset(, 1).Select
      ElseIf ActiveCell.Row < LastRow Then
        'Next row, first column
        ActiveCell.Offset(1, FirstData.Column - ActiveCell.Column).Select
      Else
        'First cell
        FirstData.Select
      End If
    End Sub
    
    Private Sub CommandButton2_Click()
      'Previous button
      If ActiveCell.Column > FirstData.Column Then
        'Same row, one step left
        ActiveCell.Offset(, -1).Select
      ElseIf ActiveCell.Row > FirstData.Row Then
        'Previous row, last column
        ActiveCell.Offset(-1, LastColumn - ActiveCell.Column).Select
      Else
        'Last cell
        Cells(LastRow, LastColumn).Select
      End If
    End Sub
    


    • Marked as answer by excelibur Thursday, November 27, 2014 7:23 PM
    Tuesday, November 25, 2014 11:02 AM

All replies

  • Hello,

    I have helped you move this thread to more related forum to get supports.

    Regards.

    Carl


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 24, 2014 6:12 AM
  • Add a Userform, add 2 TextBoxes on that form and paste this code into the code module of that form:

    Option Explicit
    Dim WithEvents WS As Worksheet
    
    Private Sub UserForm_Initialize()
      'The textboxes are only for information
      Me.TextBox1.Enabled = False
      Me.TextBox2.Enabled = False
      'Enable our event routines
      Set WS = ActiveSheet
      'Call the event routine manually the first
      WS_SelectionChange ActiveCell
    End Sub
    
    Private Sub WS_SelectionChange(ByVal Target As Range)
      Me.TextBox1 = Cells(1, ActiveCell.Column)
      Me.TextBox2 = Cells(ActiveCell.Row, 2)
    End Sub
    


    Add a regular module and paste this code:

    Sub Main()
      UserForm1.Show vbModeless
    End Sub
    

    Close the VBA editor, run sub Main and select a cell.

    Andreas.

    • Marked as answer by excelibur Monday, November 24, 2014 11:34 PM
    Monday, November 24, 2014 8:15 AM
  • Thank you so so so so so so so much! You are my hero :D

    I guess that will be a very easy question, but I couldn't it solve it at the moment: I am now trying to put 3 commandbuttons on the bottom, so you can go to the previous/next cell in the same row if you click it? But it must not go unlimited, it must be restricted also, that it will go to the  first column of the next row after the last one.

    I wrote it like that, no error-warning but it doesn't work:

    Private Sub CommandButton3_Click()
    
    Dim i As Integer
    Dim j As Integer
    
    ActiveCell.Select
    
    i = ActiveCell.Column
    j = ActiveCell.Row
    
    i = i
    j = j +1
    
    End Sub 
    



    • Edited by excelibur Monday, November 24, 2014 11:59 PM
    Monday, November 24, 2014 11:34 PM
  • I guess that will be a very easy question, but I couldn't it solve it at the moment
    Well, this is a beginner question, but anything is easy if you know how to do. :-) Study the code, read the help, debug the code, till you understand what is going on.

    BTW, here is a good tutorial:
    http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training.htm

    Andreas.

    Option Explicit
    
    Dim FirstData As Range
    Dim LastColumn As Long, LastRow As Long
    
    Private Sub UserForm_Initialize()
      'Setup the 1st data cell
      Set FirstData = Range("C2")
      'Find the last positions in the row/column headings
      LastColumn = FirstData.Offset(-1).EntireRow.Find("*", SearchDirection:=xlPrevious).Column
      LastRow = FirstData.Offset(, -1).EntireColumn.Find("*", SearchDirection:=xlPrevious).Row
    End Sub
    
    Private Sub CommandButton1_Click()
      'Next button
      If ActiveCell.Column < LastColumn Then
        'Same row, one step right
        ActiveCell.Offset(, 1).Select
      ElseIf ActiveCell.Row < LastRow Then
        'Next row, first column
        ActiveCell.Offset(1, FirstData.Column - ActiveCell.Column).Select
      Else
        'First cell
        FirstData.Select
      End If
    End Sub
    
    Private Sub CommandButton2_Click()
      'Previous button
      If ActiveCell.Column > FirstData.Column Then
        'Same row, one step left
        ActiveCell.Offset(, -1).Select
      ElseIf ActiveCell.Row > FirstData.Row Then
        'Previous row, last column
        ActiveCell.Offset(-1, LastColumn - ActiveCell.Column).Select
      Else
        'Last cell
        Cells(LastRow, LastColumn).Select
      End If
    End Sub
    


    • Marked as answer by excelibur Thursday, November 27, 2014 7:23 PM
    Tuesday, November 25, 2014 11:02 AM
  • Thanks man, you helped so much!

    • Edited by excelibur Thursday, November 27, 2014 8:00 PM
    Thursday, November 27, 2014 7:26 PM