Private Sub UserForm_Initialize() Dim MasterCompanyRange As Range Dim MasterRegionRange As Range Dim iCell As Range 'fill company list Set MasterCompanyRange = ThisWorkbook.Names("job_table").RefersToRange.Rows(1).Cells For Each iCell In MasterCompanyRange If iCell.Address = iCell.MergeArea.Cells(1, 1).Address Then Me.CompanyList.AddItem iCell.Value End If Next iCell 'preselect 1st company in list Me.CompanyList.Value = MasterCompanyRange.Cells(1, 1).Value 'fill region list Set MasterRegionRange = ThisWorkbook.Names("job_table").RefersToRange.Cells(1, 1).Offset(1, 0).Resize(1, ThisWorkbook.Names("region_count").RefersToRange.Value) For Each iCell In MasterRegionRange Me.RegionList.AddItem iCell.Value Next iCell 'preselect 1st region in list Me.RegionList.Value = MasterRegionRange.Cells(1, 1).Value End SubThe UserForm also has an "Okay" button. If I tab through the ListBox before hitting "Okay", it works fine. If I accept the default value and just hit "Okay" then sometimes the ListBox passes its highlighted value into my global variable and sometimes it passes nothing. What could possibly be the difference?
Private Sub OkayButton_Click() CreateJobField_Company = Me.CompanyList.Value CreateJobField_Region = Me.RegionList.Value Unload Me End SubAny clues would be much appreciated.
VB: Private Sub posType_Change() If Me.posType.Value = "Feature End " Then Me.feNumber.Visible = True Else Me.feNumber.Visible = False End If End Sub If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesThis is pasted in my vba before the userform initializes. I know the above was looking for the exact text but I thought if I could identify that then I'd be able to tweak the code to just see if it contained the text string.
VB: Me.StartUpPosition = 2 '(center on screen) Me.Height = Application.Height Me.Width = Application.Width If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marinesnow on that form I have 2 text boxes and and OK command button, and I cannot seem to get them centered since they stay in the position I put them in the form (design mode) and even if I was able to get them centered for my screen they would not on anyone elses, so I am trying to get them centered horezontaly in the initialization of the form.
VB: Private Sub Combo_Box_Leadoff_Click() Dim counter As Integer Combo_Box_Leadoff.Clear For counter = 3 To 16 Combo_Box_Leadoff.AddItem Worksheets(5).Cells(counter, 2).Value Next counter If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesIn the properties window, I set the value equal to the first name in the list. The above code yields a combobox that is blank except for the value set in the properties window. When I do not set an initial value, I get a completely blank combobox.
VB: Private Sub UserForm_Initialize() Dim MyUniqueList As Variant, i As Long With Me.ListBox1 .Clear ' clear the listbox content MyUniqueList = UniqueItemList(Range("o4:o100"), True) For i = 1 To UBound(MyUniqueList) .AddItem MyUniqueList(i) Next i .ListIndex = 0 ' select the first item End With End Sub Private Function UniqueItemList(InputRange As Range, _ HorizontalList As Boolean) As Variant Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant Application.Volatile On Error Resume Next For Each cl In InputRange If cl.Formula "" Then cUnique.Add cl.Value, CStr(cl.Value) End If Next cl UniqueItemList = "" If cUnique.Count > 0 Then Redim uList(1 To cUnique.Count) For i = 1 To cUnique.Count uList(i) = cUnique(i) Next i UniqueItemList = uList If Not HorizontalList Then UniqueItemList = _ Application.WorksheetFunction.Transpose(UniqueItemList) End If End If On Error Goto 0 End Function If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB: salesReportTitleLabel.Caption = "Sales report for " & Date() If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesI'm not well versed in VBA at all and I have a mediocre grasp of VB.net. I imagine I have the syntax wrong or something stupid. Any help would be greatly appreciated.