Chunk
12-28-2016, 11:29 AM
I have 10 project worksheets that are all formatted the same.
The user selects the desired project from a combo box (cb_project)
Each project has unique date ranges that populate another combo box (cb_DDate) when the project is selected (the last date closest to TODAY)
I would like cb_PDate_change event to populate 25 or so text boxes with data
I am receiving a “Run-time error ‘91’: Object variable or With block variable not set” on 2 of the projects, when the code attempts to populate the text boxes. The other 8 work fine.
Here is my code:
Private Sub cbo_project_Change()
Worksheets(cbo_project.Text).Activate
'Populates Avail Type dependant on project selected
FindString = cbo_project.Value
If Trim(FindString) <> "" Then
With Sheets("LookUpList").Range("A2:A30")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
cb_AvlType.Text = Rng(1, 3)
End If
End With
End If
'Populates dates dependant on project selected
Range("L3", Range("L" & Rows.Count).End(xlUp)).Name = "Dynamic"
cb_DDate.RowSource = "Dynamic"
'Selects last date with data dependant on project selected
Mem = -1
With cb_DDate
For i = 1 To .ListCount - 1
If CDate(.List(i)) < Date Then
If Mem = -1 Then Mem = i
If CDate(.List(Mem)) < CDate(.List(i)) Then
Mem = i
End If
End If
Next i
.ListIndex = Mem
End With
End Sub
Private Sub cb_DDate_Change()
cb_DDate = Format(cb_DDate, "mm/dd/yy")
Call mod_Data.Change
End Sub
Sub Change()
'Dim ws As Worksheet
Dim Rng As Range
Dim FindString As String
Set ws = ThisWorkbook.Worksheets(UserForm2.cbo_project.Value)
'Worksheets(UserForm2.cbo_project.Text).Activate
FindString = UserForm2.cb_DDate.Value
If Trim(FindString) <> "" Then
Set Rng = ws.Cells.Find( _
What:=CDate(FindString), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
UserForm2.tb_Totcert.Text = Rng(1, 19)
UserForm2.tb_Totcertplot.Text = Rng(1, 20)
UserForm2.tb_totcertrem.Text = Rng(1, 21)
UserForm2.tb_Totcrtremprct.Text = Rng(1, 23)
UserForm2.tb_Totcrtremprct.Value = Format(UserForm2.tb_Totcrtremprct.Value, "0.0%")
"UserForm2.tb_Totcert.Text = Rng(1, 19)" is where it craps the bed. Any ideas?
Thanks in advance.
Chunk
The user selects the desired project from a combo box (cb_project)
Each project has unique date ranges that populate another combo box (cb_DDate) when the project is selected (the last date closest to TODAY)
I would like cb_PDate_change event to populate 25 or so text boxes with data
I am receiving a “Run-time error ‘91’: Object variable or With block variable not set” on 2 of the projects, when the code attempts to populate the text boxes. The other 8 work fine.
Here is my code:
Private Sub cbo_project_Change()
Worksheets(cbo_project.Text).Activate
'Populates Avail Type dependant on project selected
FindString = cbo_project.Value
If Trim(FindString) <> "" Then
With Sheets("LookUpList").Range("A2:A30")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
cb_AvlType.Text = Rng(1, 3)
End If
End With
End If
'Populates dates dependant on project selected
Range("L3", Range("L" & Rows.Count).End(xlUp)).Name = "Dynamic"
cb_DDate.RowSource = "Dynamic"
'Selects last date with data dependant on project selected
Mem = -1
With cb_DDate
For i = 1 To .ListCount - 1
If CDate(.List(i)) < Date Then
If Mem = -1 Then Mem = i
If CDate(.List(Mem)) < CDate(.List(i)) Then
Mem = i
End If
End If
Next i
.ListIndex = Mem
End With
End Sub
Private Sub cb_DDate_Change()
cb_DDate = Format(cb_DDate, "mm/dd/yy")
Call mod_Data.Change
End Sub
Sub Change()
'Dim ws As Worksheet
Dim Rng As Range
Dim FindString As String
Set ws = ThisWorkbook.Worksheets(UserForm2.cbo_project.Value)
'Worksheets(UserForm2.cbo_project.Text).Activate
FindString = UserForm2.cb_DDate.Value
If Trim(FindString) <> "" Then
Set Rng = ws.Cells.Find( _
What:=CDate(FindString), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
UserForm2.tb_Totcert.Text = Rng(1, 19)
UserForm2.tb_Totcertplot.Text = Rng(1, 20)
UserForm2.tb_totcertrem.Text = Rng(1, 21)
UserForm2.tb_Totcrtremprct.Text = Rng(1, 23)
UserForm2.tb_Totcrtremprct.Value = Format(UserForm2.tb_Totcrtremprct.Value, "0.0%")
"UserForm2.tb_Totcert.Text = Rng(1, 19)" is where it craps the bed. Any ideas?
Thanks in advance.
Chunk