ComboBoxOffence reading ComboBoxActivity
 

Hi,
ComboBoxActivity is populated from a TextBox. I am now trying to get
CombBoxOffence to populate from the value in ComboBoxActivity. I am trying
the following code from Ron but ComboBoxOffence remains blank. TIA for any
help, I have tried naming the Sub _Click() and _Change() to no avail.
Thanks,
Mickey

================================================== ===========
Private Sub ComboBoxOffence_Change()

If Me.ComboBoxActivity.ListIndex = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C2:C7").Value
If Me.ComboBoxActivity.ListIndex = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
If Me.ComboBoxActivity.ListIndex = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

ComboBoxOffence reading ComboBoxActivity
 

ps. I have some 23 values to add so am trying to get it working by testing
with a few entries.

TIA,
Mickey

ComboBoxOffence reading ComboBoxActivity
 

Hi,

I think this is what you want. ListIndex is a numeric value specifying
the index (position) in the combo list whereas you are trying to compare the
list values.

You might to change "ELSEIF" to IF... ENDIF i.e each IF is terminated by
END IF rather the single END IF in your code.

Private Sub ComboBoxActivity_Change()

If Me.ComboBoxActivity.Value = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List= Sheets("Sheet5").Range("C2:C7").Value
ElseIf Me.ComboBoxActivity.Value= "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
ElseIf Me.ComboBoxActivity.Value= "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub


HTH

"MBlake" wrote:

Hi,
ComboBoxActivity is populated from a TextBox. I am now trying to get
CombBoxOffence to populate from the value in ComboBoxActivity. I am trying
the following code from Ron but ComboBoxOffence remains blank. TIA for any
help, I have tried naming the Sub _Click() and _Change() to no avail.
Thanks,
Mickey

================================================== ===========
Private Sub ComboBoxOffence_Change()

If Me.ComboBoxActivity.ListIndex = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C2:C7").Value
If Me.ComboBoxActivity.ListIndex = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
If Me.ComboBoxActivity.ListIndex = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

ComboBoxOffence reading ComboBoxActivity
 

ListIndex will always be a number between -1 and the numbers of items in the
list minus 1.

So it will never equal something like "R.Sanctioned Dectection"

--
Regards,
Tom Ogilvy

"MBlake" wrote in message
...
Hi,
ComboBoxActivity is populated from a TextBox. I am now trying to get
CombBoxOffence to populate from the value in ComboBoxActivity. I am
trying
the following code from Ron but ComboBoxOffence remains blank. TIA for
any
help, I have tried naming the Sub _Click() and _Change() to no avail.
Thanks,
Mickey

================================================== ===========
Private Sub ComboBoxOffence_Change()

If Me.ComboBoxActivity.ListIndex = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C2:C7").Value
If Me.ComboBoxActivity.ListIndex = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
If Me.ComboBoxActivity.ListIndex = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

ComboBoxOffence reading ComboBoxActivity
 

Hi Toppers,
Many thanks for your reply. I tried to suggested code but get an error on
the If Me.ComboBoxActivity line, I then changed the code to the below but
that failed in the same way.
================================================== ===========
Private Sub ComboBoxActivity_Change()

If Me.ComboBoxActivity.Value = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C2:C7").Value
End If
If Me.ComboBoxActivity.Value = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("D2:D4").Value
End If
If Me.ComboBoxActivity.Value = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

TIA,
Mickey



"Toppers" wrote in message
...
Hi,

I think this is what you want. ListIndex is a numeric value specifying
the index (position) in the combo list whereas you are trying to compare
the
list values.

You might to change "ELSEIF" to IF... ENDIF i.e each IF is terminated by
END IF rather the single END IF in your code.

Private Sub ComboBoxActivity_Change()

If Me.ComboBoxActivity.Value = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List= Sheets("Sheet5").Range("C2:C7").Value
ElseIf Me.ComboBoxActivity.Value= "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
ElseIf Me.ComboBoxActivity.Value= "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub


HTH

"MBlake" wrote:

Hi,
ComboBoxActivity is populated from a TextBox. I am now trying to get
CombBoxOffence to populate from the value in ComboBoxActivity. I am
trying
the following code from Ron but ComboBoxOffence remains blank. TIA for
any
help, I have tried naming the Sub _Click() and _Change() to no avail.
Thanks,
Mickey

================================================== ===========
Private Sub ComboBoxOffence_Change()

If Me.ComboBoxActivity.ListIndex = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C2:C7").Value
If Me.ComboBoxActivity.ListIndex = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
If Me.ComboBoxActivity.ListIndex = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

ComboBoxOffence reading ComboBoxActivity
 

Hi Toppers,
I think I have solved it, your code pointed me the right way.

Many thanks,
Mickey

ComboBoxOffence reading ComboBoxActivity
 

if those are the only three choices and they are listed in order then

Private Sub ComboBoxActivity_Change()
Select Case Me.ComboBoxActivity.ListIndex
Case 0
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C2:C7").Value
Case 1
Me.ComboBoxOffence.List = Sheets("sheet5").Range("D2:D4").Value
Case 2
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C12:C17").Value
End Select
End Sub

--
Regards,
Tom Ogilvy


"MBlake" wrote in message
...
Hi Toppers,
Many thanks for your reply. I tried to suggested code but get an error on
the If Me.ComboBoxActivity line, I then changed the code to the below but
that failed in the same way.
================================================== ===========
Private Sub ComboBoxActivity_Change()

If Me.ComboBoxActivity.Value = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C2:C7").Value
End If
If Me.ComboBoxActivity.Value = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("D2:D4").Value
End If
If Me.ComboBoxActivity.Value = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

TIA,
Mickey



"Toppers" wrote in message
...
Hi,

I think this is what you want. ListIndex is a numeric value specifying
the index (position) in the combo list whereas you are trying to compare
the
list values.

You might to change "ELSEIF" to IF... ENDIF i.e each IF is terminated
by
END IF rather the single END IF in your code.

Private Sub ComboBoxActivity_Change()

If Me.ComboBoxActivity.Value = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List= Sheets("Sheet5").Range("C2:C7").Value
ElseIf Me.ComboBoxActivity.Value= "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
ElseIf Me.ComboBoxActivity.Value= "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub


HTH

"MBlake" wrote:

Hi,
ComboBoxActivity is populated from a TextBox. I am now trying to get
CombBoxOffence to populate from the value in ComboBoxActivity. I am
trying
the following code from Ron but ComboBoxOffence remains blank. TIA for
any
help, I have tried naming the Sub _Click() and _Change() to no avail.
Thanks,
Mickey

================================================== ===========
Private Sub ComboBoxOffence_Change()

If Me.ComboBoxActivity.ListIndex = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C2:C7").Value
If Me.ComboBoxActivity.ListIndex = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
If Me.ComboBoxActivity.ListIndex = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

ComboBoxOffence reading ComboBoxActivity
 

Hi Tom,
Thanks for your advice. I'll try that one out as well.

Thanks again,
Mickey


"Tom Ogilvy" wrote in message
...
if those are the only three choices and they are listed in order then

Private Sub ComboBoxActivity_Change()
Select Case Me.ComboBoxActivity.ListIndex
Case 0
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C2:C7").Value
Case 1
Me.ComboBoxOffence.List = Sheets("sheet5").Range("D2:D4").Value
Case 2
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C12:C17").Value
End Select
End Sub

--
Regards,
Tom Ogilvy


"MBlake" wrote in message
...
Hi Toppers,
Many thanks for your reply. I tried to suggested code but get an error on
the If Me.ComboBoxActivity line, I then changed the code to the below but
that failed in the same way.
================================================== ===========
Private Sub ComboBoxActivity_Change()

If Me.ComboBoxActivity.Value = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C2:C7").Value
End If
If Me.ComboBoxActivity.Value = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("D2:D4").Value
End If
If Me.ComboBoxActivity.Value = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

TIA,
Mickey



"Toppers" wrote in message
...
Hi,

I think this is what you want. ListIndex is a numeric value
specifying
the index (position) in the combo list whereas you are trying to
compare
the
list values.

You might to change "ELSEIF" to IF... ENDIF i.e each IF is terminated
by
END IF rather the single END IF in your code.

Private Sub ComboBoxActivity_Change()

If Me.ComboBoxActivity.Value = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List= Sheets("Sheet5").Range("C2:C7").Value
ElseIf Me.ComboBoxActivity.Value= "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
ElseIf Me.ComboBoxActivity.Value= "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub


HTH

"MBlake" wrote:

Hi,
ComboBoxActivity is populated from a TextBox. I am now trying to get
CombBoxOffence to populate from the value in ComboBoxActivity. I am
trying
the following code from Ron but ComboBoxOffence remains blank. TIA
for
any
help, I have tried naming the Sub _Click() and _Change() to no avail.
Thanks,
Mickey

================================================== ===========
Private Sub ComboBoxOffence_Change()

If Me.ComboBoxActivity.ListIndex = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C2:C7").Value
If Me.ComboBoxActivity.ListIndex = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
If Me.ComboBoxActivity.ListIndex = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

ComboBoxOffence reading ComboBoxActivity
 

Hi,
I copied your code and it works OK for me. How are you populating
ComboBoxActivity? I used Userform_Initialize to set the list to the 3 values
in your example - see below.

Or does it only have one value (as you say it is populated from a textbox)?
Even then , it should work.

And what error meesage does it fail with?

Private Sub UserForm_Initialize()
UserForm1.ComboBoxActivity.List = Worksheets("Sheet1").Range("a1:a3").Value
End Sub

"MBlake" wrote:

Hi Toppers,
Many thanks for your reply. I tried to suggested code but get an error on
the If Me.ComboBoxActivity line, I then changed the code to the below but
that failed in the same way.
================================================== ===========
Private Sub ComboBoxActivity_Change()

If Me.ComboBoxActivity.Value = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C2:C7").Value
End If
If Me.ComboBoxActivity.Value = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("D2:D4").Value
End If
If Me.ComboBoxActivity.Value = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

TIA,
Mickey



"Toppers" wrote in message
...
Hi,

I think this is what you want. ListIndex is a numeric value specifying
the index (position) in the combo list whereas you are trying to compare
the
list values.

You might to change "ELSEIF" to IF... ENDIF i.e each IF is terminated by
END IF rather the single END IF in your code.

Private Sub ComboBoxActivity_Change()

If Me.ComboBoxActivity.Value = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List= Sheets("Sheet5").Range("C2:C7").Value
ElseIf Me.ComboBoxActivity.Value= "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
ElseIf Me.ComboBoxActivity.Value= "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub


HTH

"MBlake" wrote:

Hi,
ComboBoxActivity is populated from a TextBox. I am now trying to get
CombBoxOffence to populate from the value in ComboBoxActivity. I am
trying
the following code from Ron but ComboBoxOffence remains blank. TIA for
any
help, I have tried naming the Sub _Click() and _Change() to no avail.
Thanks,
Mickey

================================================== ===========
Private Sub ComboBoxOffence_Change()

If Me.ComboBoxActivity.ListIndex = "R.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C2:C7").Value
If Me.ComboBoxActivity.ListIndex = "R.Other Arrest" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("D2:D4").Value
If Me.ComboBoxActivity.ListIndex = "X.Sanctioned Detection" Then
Me.ComboBoxOffence.List = Sheets("Sheet5").Range("C12:C17").Value
End If
End Sub
================================================== ===========

For Toppers
 

Hi Toppers,
It is all working now but I have placed the code for the 'ComboBoxActivity'
below, this code populates from TextBox3. The difficulty was that (a) the
available list for 'ComboBoxOffence' had to relate to the 'UNIT' in
'TextBox3' and the Activity in 'ComboBoxActivity'. A workaround using help
from in the States has evolved the code below, I am sure this can be
tightened up but it is working at the moment so there is progress!. One
later issue will be that I will want to calculate the Sanctioned Detections
etc. on a Monthly basis but this format will give me different values for
X.Sanctioned Detections and R.Sanctioned Detections etc. I am hoping that I
can add the two values together for one monthly chart and have bought John
Walkenbach's Excel 2003 Formulas book to help out.

As an aside, can you tell me how I simply populate a ComboBox from a range
of values, I have tried -

ControlBox1.List = Array("A Div", "B Div", "C Div") but that did not work, I
added it to ControlBox1_Change()

but that failed. Here is the code to drive ComboBoxActivity -

================================================== =========================
Private Sub LoadActivityDropdown()

Dim FoundCell As Range
Dim Rng As Range
Dim UnitTxt As String
UnitTxt = Me.TextBox3.Text
Set FoundCell = Range("UnitTypeList").Find(what:=UnitTxt,
after:=Range("UnitTypeList")(1, 1), lookat:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
With Me.ComboBoxActivity
.Clear
Select Case FoundCell.Text
Case "RPU 1", "RPU 2", "RPU 3", "RPU 4", "RPU 5"
.AddItem "R.Sanctioned Detection"
.AddItem "R.Other Arrest"
.AddItem "R.Roads Policing"
.AddItem "R.Speeding"
.AddItem "R.ANPR Other"
.AddItem "R.POMAN Other"
Case "RPU 6", "RPU 7", "Enforcement"
.AddItem "Sanctioned Detection"
.AddItem "X.Other Arrest"
.AddItem "X.Roads Policing"
.AddItem "X.Speeding"
.AddItem "X.ANPR Other"
.AddItem "X.POMAN Other"
Case "TVCU"
.AddItem "T.Sanctioned Detection"
.AddItem "T.Other Arrest"
.AddItem "T.POMAN Other"
Case "MetroLink"
.AddItem "M.Sanctioned.Detection"
.AddItem "M.Other Arrest"
.AddItem "M.POMAN Other"
.AddItem "M.Visibility"
Case "Traffic Warden"
.AddItem "W.Speeding"
.AddItem "W.POMAN Other"
.AddItem "W.Visiblity"
Case "Investigation", "OPU", "TMU", "Policy Unit", "Accident
Records", "Camera Enforcement", "NRSI", "Admin"
.AddItem "Not Applicable"
Case Else
End Select
End With
End If
End Sub
================================================== ===================================

Regards,
Mickey