ListBox column count VBA Excel
|
Private Sub CommandButton1_Click()
With UserForm1
.TextBox1.Text = ListBox1.List(ListBox1.ListIndex)
.TextBox2.Text = ListBox1.List(ListBox1.ListIndex, 1)
.TextBox3.Text = ListBox1.List(ListBox1.ListIndex, 2)
.TextBox4.Text = ListBox1.List(ListBox1.ListIndex, 3)
.TextBox5.Text = ListBox1.List(ListBox1.ListIndex, 4)
.TextBox6.Text = ListBox1.List(ListBox1.ListIndex, 5)
End With
End Sub Since you are only using TestBox1" as a display for the User to select from:In "ListBox 1" keep one column, separate the itmes (in one string) with " | " . Keep all the items in all the Rows in an array. In cbtUpDate_Click Private Sub CommandButton1_Click()
Dim RowNum
ArrIndex = Me.tbxRecords.ListIndex - 1
With Me
.tbxRecordRing1.Text = arrRecords(arrindex, 0)
.TtbxRecordHole1.Text =arrRecords(arrindex, 1)
.tbxRecordDepth.Text =arrRecords(arrindex, 2)
.tbxRecordChangeCode14.Text = arrRecords(arrindex, 3)
.tbxRecordChangeCode2.Text = arrRecords(arrindex, 4)
.tbxRecordDescription1.Text = arrRecords(arrindex, 5)
End With
' The names are out of order, but that is the way to name controls!
'If you want really well structured code. Create an Enum in modGlobals for all twelve of the items.
End Sub Enum ItemArrayIndexNumber
ndxRecordLocation1 = 0
ndxRecordRing1
ndxRecordHole1
ndxRecordDepth2
ndxProductionCodes1
ndxProductionDetail1
ndxRecordChangeCode1
ndxRecordChangeCode2
ndxRecordDescription1
ndxRecordChangeCode1RT
ndxRecordChangeCode2RT
ndxRecordChangeCode2RT
End Enum
' I know, the names are out of order This changes the sample code above to read like.tbxRecordRing1.Text = arrRecords(arrindex, .ndxRecordRing1) Note how this makes your code "Self Correcting"."Look, I'm working on Recording Ring one. Yep, the index is Recording Ring one. OK!" My finale bits of programming practices advice. name a Module holding you Global Variables "modGlobals," and prefix Class Module names with "cls" or "obj." Public GlobalData(6000) As objRowReference
'Instantiated in UserForm1 Private Sub cmdAdd_Click()
'Used in:
'UserForm1 Private Sub btnSave_Click()
'UserForm1 Private Sub btnNew_Click()
'UserForm1 Private Sub ListBox3_DblClick This is atrocious Naming system, most especially when you don't even have 27 textbox controls. arrCtrls = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, _
TextBox22, TextBox23, TextBox24, TextBox25, TextBox26, TextBox27)
|
