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