Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Option Button & List Boxes

Status
Not open for further replies.
Harley78

Harley78

Electrical
May 8, 2007
77
US
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
 
Replies continue below

Recommended for you

Sort by date Sort by votes
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
 
 http://files.engineering.com/getfile.aspx?folder=531436bc-972f-4b5d-ba59-4d1040976a9e&file=UserForm_Sample.xls
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

vinodnx7
  • Locked
  • Question
count the same text from the file and create new list with number
Replies
19
Views
119
vinodnx7
vinodnx7
kyleShropshire
  • Locked
  • Question
Spreadsheet bill of materials and separate parts lists
Replies
4
Views
23
3DDave
3DDave
iken
  • Locked
  • Question
Data Validation Lists Using Dynamic Name Range
Replies
6
Views
48
SkipVought
SkipVought
jmarkus
  • Locked
  • Question
MS Excel - Drop Down Lists that result in links to original list cells 4
2
Replies
33
Views
54
SkipVought
SkipVought
Blazerdog
  • Locked
  • Question
List lookup 2
Replies
3
Views
17
Blazerdog
Blazerdog

Part and Inventory Search

Sponsor

Back
Top