Option Button & List Boxes

[Harley78]

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

 

[cummings54]

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