Linking Comboboxes

[Oreg]

Hello,


Got a combobox question. Ive created a form listing dates (combobox1)
and ticket numbers (combobox2) on a sheet. I've set the rowsource fo
both comboxes and info shows up just fine when form is opened. I
there code I can add that when a date is selected in combobox1
combobox2 will change to the related ticket number? Also, could
scroll bar be added which when pulled down, will scroll down column A?

 

[Tom Ogilvy]

Don't use the rowsource for combobox2
Use the click event of combobox1 to loop through the data and use additem to
add the ticket numbers that match the data selected.


You can put a scroll bar on the userform and write code to scroll down
column A.

 

[Oreg]

Thanks Tom for your reply.

Could you possibly step my through doing that. I only know enough t
be dangerous!!! Thanks for pointing me in the right direction

 

[Tom Ogilvy]

Private Sub combobbox1_Click()
Dim dt as Long
With Userform1
if .Combobox1.ListIndex = -1 then exit sub
.Combobox2.RowSource = ""
.Combobox2.clear
dt = clng(cdate(.Combobox1.Value))
for each cell in Worksheets("Data").Range("A1:A200")
if cell.Value2 = dt then
.combobox2.AddItem cell.offset(0,1).Value
end if
Next
End With
End Sub

 

[Oreg]

Hi Tom,

Can't thank you enough. One thing I would like to change if possible.
When I click on combobox1 to get a date, I have to also click o
combobox2 for the related ticket number to populate. Any way to aut
poulate the ticket field? Also, If not to much trouble, would you kno
a good place to start to get info on creating the scroll bar yo
discussed earlier??

Thanks in advanc

 

[Tom Ogilvy]

If you only have one ticket number per date, you don't need the second item
to be a combobox. It should be a textbox

Private Sub combobox1_Click()
Dim dt as Long
With Userform1
if .Combobox1.ListIndex = -1 then exit sub
.Combobox2.RowSource = ""
.Combobox2.clear
dt = clng(cdate(.Combobox1.Value))
for each cell in Worksheets("Data").Range("A1:A200")
if cell.Value2 = dt then
Textbox2.Value = cell.offset(0,1).Text
end if
Next
End With
End Sub

there are a lot of ways you could approach this. Here is one way.

Private Sub ScrollBar1_Change()
Dim rng As Range, Rng1 As Range
Dim lRow As Long, Rng2 As Range

With ActiveSheet
Set rng = .Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
End With
ScrollBar1.Max = rng(rng.Rows.Count).Row
ScrollBar1.Min = 1

Set Rng1 = rng(ScrollBar1.Value)
Set Rng2 = ActiveWindow.VisibleRange.EntireRow
If Intersect(Rng1, ActiveWindow.VisibleRange.EntireRow) _
Is Nothing Then
lRow = Rng1.Row - Rng2.Rows.Count / 2
If lRow < 1 Then lRow = 1
ActiveWindow.ScrollRow = lRow
End If

End Sub

 

[Bob Phillips]

Oreg,

If you want a sample workbook of linked comboboxes, drop me your email
address and I will mail it to you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Oreg]

Tom,

Works Great!!! Thanks for taking the time to show me. I' can't ge
the scroll bar to work..but that may be because I have combobox & tex
box inside a frame...still working with it to see what the problem is.
I never thought I would get this far though. Can't thank you enough.


Bob,

That would be great. My email is (e-mail address removed) Lookin
forward to practicing with the workbook. Thanks for all the help

 

[Oreg]

I noticed that I can view the date & ticket number but am not able t
make changes to the ticket number if needed. Is there a simple wa
around this?


Thanks again,

Ore

 

[Tom Ogilvy]

As written, it doesn't scroll until the row number is beyond the visible
range. Then it centers that row vertically in the visible range. If you
don't have values out of view, it won't do anything. It isn't a smooth and
continuous scroll. As I said, there are many ways this could be
implemented - it really would require knowledge of what you are trying to
achieve. (which you have and I don't. )

 

[Oreg]

I see what you mean Tom. The scroll bar was working only I didn't kno
it. I thought the scroll bar would scroll down the list within th
form. Trying to understand vb...new to it as of last week. I gues
With activesheet kind of gave it away huh?


Ore

 

[Tom Ogilvy]

You said you wanted to scroll column A.

If you want to scroll the list in the combobox, then you should get that
automatically if the list is longer than what can be viewed. (the dropdown
for the combobox will have a vertical scroll bar.

 

[Tom Ogilvy]

There is no relationship between what is in the textbox and a cell. If you
want to update the cell, you need to use the exit event (as an example) of
the textbox to write back to the cell.

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Worksheets("Data")
set rng = .Range("A1:A200")
End With
res = Application.Match(clng(combobox1.Value),rng,0)
if not iserror(res) then
rng(res).Offset(0,1).Value = Textbox2.Text
end if

End Sub