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?

--
Message posted from http://www.ExcelForum.com

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.

--
Regards,
Tom Ogilvy

"Oreg " wrote in message
...
Hello,


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


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Regards,
Tom Ogilvy


"Oreg " wrote in message
...
Thanks Tom for your reply.

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


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Regards,
Tom Ogilvy


"Oreg " wrote in message
...
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 on
combobox2 for the related ticket number to populate. Any way to auto
poulate the ticket field? Also, If not to much trouble, would you know
a good place to start to get info on creating the scroll bar you
discussed earlier??

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

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 " wrote in message
...
Hello,


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


---
Message posted from http://www.ExcelForum.com/

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 Lookin
forward to practicing with the workbook. Thanks for all the help

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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. )

--
Regards,
Tom Ogilvy



"Oreg " wrote in message
...
Tom,

Works Great!!! Thanks for taking the time to show me. I' can't get
the scroll bar to work..but that may be because I have combobox & text
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 Looking
forward to practicing with the workbook. Thanks for all the help!


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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.

--
Regards,
Tom Ogilvy

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


Oreg


---
Message posted from http://www.ExcelForum.com/

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

--
Regards,
Tom Ogilvy


"Oreg " wrote in message
...
I noticed that I can view the date & ticket number but am not able to
make changes to the ticket number if needed. Is there a simple way
around this?


Thanks again,

Oreg


---
Message posted from http://www.ExcelForum.com/