×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Option Button & List Boxes

Option Button & List Boxes

Option Button & List Boxes

(OP)
How is the code written so that when a userform  is open that it shows what was in the cell that it refers to?

I have 6 yes & 6 no option buttons and 8 list boxes.

The code I have thus far reflects what should be filled and where it should go on the worksheet, but don't recall how to make it remember what is in the cell. or where to place it in the code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim UForm As UserForm1
    Dim Sel_Row As Long
    Dim Cont_Item As MSForms.Control
    Dim Col_Name As String
    Dim ListBox_Item As MSForms.ListBox
    Dim Cnt1 As Integer
    Dim Cnt2 As Integer
    Dim Flag
    Dim L_Item As ListItem

    Cancel = True

    Set UForm = New UserForm1

    Sel_Row = Target.Row
    
    'POPULATE THE USERFORM FIELDS
        
    'TEXT BOXES
    UForm.TextBox1.Value = Me.Range("AQ" & Sel_Row).Value
    UForm.TextBox2.Value = Me.Range("AR" & Sel_Row).Value
    UForm.TextBox3.Value = Me.Range("AS" & Sel_Row).Value
    UForm.TextBox4.Value = Me.Range("AT" & Sel_Row).Value
    UForm.TextBox5.Value = Me.Range("AU" & Sel_Row).Value
    UForm.TextBox6.Value = Me.Range("AV" & Sel_Row).Value
    UForm.TextBox7.Value = Me.Range("AW" & Sel_Row).Value
    UForm.TextBox8.Value = Me.Range("AX" & Sel_Row).Value
    UForm.TextBox9.Value = Me.Range("DH" & Sel_Row).Value
    UForm.TextBox10.Value = Me.Range("AO" & Sel_Row).Value
    
    
   '    OPTION BUTTONS

    For Each Cont_Item In UForm.Controls
    
        If TypeName(Cont_Item) = "OptionButton" Then
        
            Col_Name = ""
            
            Select Case Cont_Item.Name
            
                Case UForm.OptionButton5.Name
                    UForm.OptionButton4.Value = True
                    Col_Name = "CR"
                    
                Case UForm.OptionButton8.Name
                    UForm.OptionButton9.Value = True
                    Col_Name = "CS"
                    
                Case UForm.OptionButton10.Name
                    UForm.OptionButton11.Value = True
                    Col_Name = "CT"
                    
                Case UForm.OptionButton12.Name
                    UForm.OptionButton13.Value = True
                    Col_Name = "CU"
                    
                Case UForm.OptionButton14.Name
                    UForm.OptionButton15.Value = True
                    Col_Name = "CV"
                    
                Case UForm.OptionButton16.Name
                    UForm.OptionButton17.Value = True
                    Col_Name = "CX"
                    
                                 
            End Select
                
            If Col_Name <> "" Then
            
                If StrComp(Sheet4.Range(Col_Name & Sel_Row).Value, "Y", vbTextCompare) = 0 Then
                
                    Cont_Item.Value = True
                    
                End If
                
            End If
            
        End If
            
    Next Cont_Item

  
 
    'POPULATE LIST BOXES
    
    UForm.ListBox3.List = Sheet4.Range("C2:C5").Value
    UForm.ListBox5.List = Sheet4.Range("G2:G10").Value
    UForm.ListBox6.List = Sheet4.Range("I2:I9").Value
    UForm.ListBox4.List = Sheet4.Range("E2:E4").Value
    UForm.ListBox7.List = Sheet4.Range("K2:K5").Value
    UForm.ListBox8.List = Sheet4.Range("M2:M4").Value
    UForm.ListBox9.List = Sheet4.Range("R2:R10").Value
    UForm.ListBox10.List = Sheet4.Range("Q2:Q5").Value
    
    'ListVIEW1 needs to be treated by itself
    
    UForm.ListView1.View = lvwReport
    UForm.ListView1.AllowColumnReorder = True
    UForm.ListView1.Gridlines = True
    
    UForm.ListView1.ColumnHeaders.Add Text:="Qty"
    UForm.ListView1.ColumnHeaders.Add Text:="Module Name"
    UForm.ListView1.ColumnHeaders.Add Text:="Column Name"
    
    UForm.ListView1.ColumnHeaders.Item(3).Width = 0
    
    For Cnt1 = 52 To 93
        Set L_Item = UForm.ListView1.ListItems.Add(Text:=Me.Cells(Sel_Row, Cnt1).Value)
        
        L_Item.SubItems(1) = Me.Cells(1, Cnt1).Value
        L_Item.SubItems(2) = Alpha(Cnt1)
        
    Next Cnt1
        
    
    'SELECT IN LISTS
    
    For Each Cont_Item In UForm.Controls
    
        If TypeName(Cont_Item) = "ListBox" Then
            
            Set ListBox_Item = Cont_Item
            
            Col_Name = ""
            
            Select Case Cont_Item.Name
            
                Case UForm.ListBox3.Name
                    Col_Name = "CW"
                Case UForm.ListBox4.Name
                    Col_Name = "DC"
                Case UForm.ListBox5.Name
                    Col_Name = "CY"
                Case UForm.ListBox6.Name
                    Col_Name = "CZ"
                Case UForm.ListBox7.Name
                    Col_Name = "DD"
                Case UForm.ListBox8.Name
                    Col_Name = "DE"
                Case UForm.ListBox9.Name
                    Col_Name = "DF"
                Case UForm.ListBox10.Name
                    Col_Name = "AY"
                    
            End Select
            
            If Sheet4.Range(Col_Name & Sel_Row).Value <> "" Then
                
                Flag = True
            
                For Cnt2 = 0 To ListBox_Item.ListCount - 1
                
                    If ListBox_Item.List(Cnt2) = Sheet4.Range(Col_Name & Sel_Row).Value Then
                    
                        Flag = False
                        
                        ListBox_Item.Selected(Cnt2) = True
                        
                        Exit For
                        
                    End If
                    
                Next Cnt2
                
                If Flag Then
                    
                    ListBox_Item.AddItem Me.Range(Col_Name & Sel_Row).Value
                    
                    ListBox_Item.Selected(ListBox_Item.ListCount - 1) = True
                    
                    MsgBox "I added " & Me.Range(Col_Name & Sel_Row).Value & " to " & ListBox_Item.Tag & " even though it isn't in the original list...", , "Oops"
                    
                End If
                
            End If
                
            Set ListBox_Item = Nothing
            
        End If
            
    Next Cont_Item
    
    Manual_Quit = False
    UForm.Show
    
    If Manual_Quit Then
        
        Exit Sub
        
    End If
    
    'POPULATE FROM USERFORM
    
    'TEXT BOXES
    Me.Range("AQ" & Sel_Row).Value = UForm.TextBox1.Value
    Me.Range("AR" & Sel_Row).Value = UForm.TextBox2.Value
    Me.Range("AS" & Sel_Row).Value = UForm.TextBox3.Value
    Me.Range("AT" & Sel_Row).Value = UForm.TextBox4.Value
    Me.Range("AU" & Sel_Row).Value = UForm.TextBox5.Value
    Me.Range("AV" & Sel_Row).Value = UForm.TextBox6.Value
    Me.Range("AW" & Sel_Row).Value = UForm.TextBox7.Value
    Me.Range("AX" & Sel_Row).Value = UForm.TextBox8.Value
    Me.Range("DH" & Sel_Row).Value = UForm.TextBox9.Value
    Me.Range("AO" & Sel_Row).Value = UForm.TextBox10.Value
    
    'OPTION BUTTONS
    For Each Cont_Item In UForm.Controls
    
        If TypeName(Cont_Item) = "OptionButton" Then
        
            Col_Name = ""
            
            Select Case Cont_Item.Name
            
                Case UForm.OptionButton5.Name
                    Col_Name = "CR"
                    
                Case UForm.OptionButton8.Name
                    Col_Name = "CS"
                    
                Case UForm.OptionButton10.Name
                    Col_Name = "CT"
                    
                Case UForm.OptionButton12.Name
                    Col_Name = "CU"
                    
                Case UForm.OptionButton14.Name
                    Col_Name = "CV"
                    
                Case UForm.OptionButton16.Name
                    Col_Name = "CX"
                    
                       
            End Select
                
            If Col_Name <> "" Then
            
                If Cont_Item.Value = True Then
                
                    Me.Range(Col_Name & Sel_Row).Value = "Y"
                    
                Else
                
                    Me.Range(Col_Name & Sel_Row).Value = "N"
                    
                End If
                
            End If
            
        End If
            
    Next Cont_Item
    
    'LIST VIEW
    
    Cnt1 = 52
    
    For Each L_Item In UForm.ListView1.ListItems
    
        Me.Range(L_Item.SubItems(2) & Sel_Row).Value = L_Item.Text
        
        Cnt1 = Cnt1 + 1
        
    Next L_Item
    
    'LIST BOXES
    
    For Each Cont_Item In UForm.Controls
    
        If TypeName(Cont_Item) = "ListBox" Then
            
            Set ListBox_Item = Cont_Item
            
            Col_Name = ""
            
            Select Case Cont_Item.Name
            
                Case UForm.ListBox3.Name
                    Col_Name = "CW"
                Case UForm.ListBox4.Name
                    Col_Name = "DC"
                Case UForm.ListBox5.Name
                    Col_Name = "CY"
                Case UForm.ListBox6.Name
                    Col_Name = "CZ"
                Case UForm.ListBox7.Name
                    Col_Name = "DD"
                Case UForm.ListBox8.Name
                    Col_Name = "DE"
                Case UForm.ListBox9.Name
                    Col_Name = "DF"
                Case UForm.ListBox10.Name
                    Col_Name = "AY"
                    
            End Select
            
            For Cnt2 = 0 To ListBox_Item.ListCount - 1
            
                If ListBox_Item.Selected(Cnt2) = True Then
                
                    Me.Range(Col_Name & Sel_Row).Value = ListBox_Item.List(Cnt2)
                    
                    Exit For
                    
                End If
                
            Next Cnt2
            
            Set ListBox_Item = Nothing
            
        End If
            
    Next Cont_Item
    
    
    Set UForm = Nothing


End Sub

RE: Option Button & List Boxes

Have you looked at the ControlSource property? Listboxes and option buttons on a userform have a built-in link to the worksheet through the ControlSource property. When using a UserForm the control source needs a worksheet reference as well as a cell reference so the ControlSource text string look like Sheet1!A1 etc.

The sample workbook has a userform and a doubleclick event procedure in Sheet1 that shows how to use the control source property to populate the userform and return data back to teh worksheet.

HTH

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources