Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > ComboBoxOffence reading ComboBoxActivity
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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
================================================== ===========


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,339
Default 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
================================================== ===========



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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
================================================== ===========




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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
================================================== ===========





Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default ComboBoxOffence reading ComboBoxActivity
Hi Toppers,
I think I have solved it, your code pointed me the right way.

Many thanks,
Mickey


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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
================================================== ===========







Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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
================================================== ===========









Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,339
Default 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
================================================== ===========






Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
reading from SQL Texas Tonie[_2_] Excel Discussion (Misc queries) 0 November 9th 07 10:59 PM
Reading XML Ajit Excel Discussion (Misc queries) 1 October 2nd 07 10:54 PM
reading statements mike Excel Programming 4 February 20th 04 01:47 PM
reading xls from asp mrweb Excel Programming 0 October 20th 03 08:54 PM
sub for reading Fernando Duran Excel Programming 4 September 15th 03 07:52 PM


All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement