I have a worksheet that displays Example Problems. The worksheet has hundreds
of rows of text and text boxes. I created a combobox that lists each Example
Problem so the user can click on the problem he/she wants and the macro will
take them to that problem (a certain row in the worksheet). To help the user
get back to the top, I created Label names "^top" at the end of each Example
Problem. This Label takes the user back to the top of the worksheet (A1).
However, when I do this, the combobox remains with the item the user last
clicked on. I would like for the combobox to display the first Example
Problem because the first Example Problem begins on cell A1. I also have
protected the sheet., so if I delete the box I get an error. I tried
unprotect/protect in my macro, but I would rather be able to change the
combobox without deleting it. The code for my combobox is:

Private Sub ComboBox1()
With Worksheets(45).Shapes.AddFormControl(xlDropDown, _
Left:=440, Top:=47, Width:=240, Height:=15)
.ControlFormat.DropDownLines = 9
.ControlFormat.AddItem "Example 1: Method 1, Circular Corrugated Pipe", 1
.ControlFormat.AddItem "Example 2: Method 1, Deformed Smooth-Interior
Pipe", 2
.ControlFormat.AddItem "Example 3: Method 1, Circular Smooth-Interior
Pipe", 3
.ControlFormat.AddItem "Example 4: Method 2, Circular Corrugated Pipe", 4
.ControlFormat.AddItem "Example 5: Method 2, Circular Corrugated Pipe
(SPM)", 5
.ControlFormat.AddItem "Example 6: Method 2, Deformed Corrugated Pipe", 6
.ControlFormat.AddItem "Example 7: Method 3, Circular Corrugated Pipe", 7
.ControlFormat.AddItem "Example 8: Method 3, Deformed Corrugated Pipe
(SPAA)", 8
.ControlFormat.AddItem "Example 9: Method 3, Deformed Corrugated Pipe
(SPS)", 9
.Name = "ComboBox1"
.OnAction = "ComboBox1_Change"
End With
End Sub

The code for the "^top" button is simply a Sub with Range("A1").Select

Any help would be greatly appreciated.

--
Message posted via http://www.officekb.com

Your ^top macro should include a line of code like

activesheet.dropdowns("Combobox1").listIndex = 1
Range("A1").Select

--
Regards,
Tom Ogilvy


"mtm4300 via OfficeKB.com" wrote:

I have a worksheet that displays Example Problems. The worksheet has hundreds
of rows of text and text boxes. I created a combobox that lists each Example
Problem so the user can click on the problem he/she wants and the macro will
take them to that problem (a certain row in the worksheet). To help the user
get back to the top, I created Label names "^top" at the end of each Example
Problem. This Label takes the user back to the top of the worksheet (A1).
However, when I do this, the combobox remains with the item the user last
clicked on. I would like for the combobox to display the first Example
Problem because the first Example Problem begins on cell A1. I also have
protected the sheet., so if I delete the box I get an error. I tried
unprotect/protect in my macro, but I would rather be able to change the
combobox without deleting it. The code for my combobox is:

Private Sub ComboBox1()
With Worksheets(45).Shapes.AddFormControl(xlDropDown, _
Left:=440, Top:=47, Width:=240, Height:=15)
.ControlFormat.DropDownLines = 9
.ControlFormat.AddItem "Example 1: Method 1, Circular Corrugated Pipe", 1
.ControlFormat.AddItem "Example 2: Method 1, Deformed Smooth-Interior
Pipe", 2
.ControlFormat.AddItem "Example 3: Method 1, Circular Smooth-Interior
Pipe", 3
.ControlFormat.AddItem "Example 4: Method 2, Circular Corrugated Pipe", 4
.ControlFormat.AddItem "Example 5: Method 2, Circular Corrugated Pipe
(SPM)", 5
.ControlFormat.AddItem "Example 6: Method 2, Deformed Corrugated Pipe", 6
.ControlFormat.AddItem "Example 7: Method 3, Circular Corrugated Pipe", 7
.ControlFormat.AddItem "Example 8: Method 3, Deformed Corrugated Pipe
(SPAA)", 8
.ControlFormat.AddItem "Example 9: Method 3, Deformed Corrugated Pipe
(SPS)", 9
.Name = "ComboBox1"
.OnAction = "ComboBox1_Change"
End With
End Sub

The code for the "^top" button is simply a Sub with Range("A1").Select

Any help would be greatly appreciated.

--
Message posted via http://www.officekb.com

When I use this code I get a Run-Time Error '1004'

It reads " Unable to get Dropdowns Property of The Worksheet Class"

any ideas?
Thank You.

Tom Ogilvy wrote:
Your ^top macro should include a line of code like

activesheet.dropdowns("Combobox1").listIndex = 1
Range("A1").Select

I have a worksheet that displays Example Problems. The worksheet has hundreds
of rows of text and text boxes. I created a combobox that lists each Example
[quoted text clipped - 35 lines]

Any help would be greatly appreciated.

--
Message posted via http://www.officekb.com

I ran your code and my event code looked like this:

Sub ComboBox1_Change()
ActiveSheet.DropDowns("Combobox1").ListIndex = 1
Range("A1").Select
End Sub

worked fine for me.

I ran it in a separate routine and it again ran perfectly.
Sub AAA()
ActiveSheet.DropDowns("Combobox1").ListIndex = 1
Range("A1").Select

End Sub

It appears to be some implementation problem on your part. Maybe you have
put it in a sheet module or something - I can't say. But there is nothing
wrong with the suggestion.

--
Regards,
Tom Ogilvy



"mtm4300 via OfficeKB.com" <u18572@uwe wrote in message
news:5d58cf7c5355d@uwe...
When I use this code I get a Run-Time Error '1004'

It reads " Unable to get Dropdowns Property of The Worksheet Class"

any ideas?
Thank You.

Tom Ogilvy wrote:
Your ^top macro should include a line of code like

activesheet.dropdowns("Combobox1").listIndex = 1
Range("A1").Select

I have a worksheet that displays Example Problems. The worksheet has
hundreds
of rows of text and text boxes. I created a combobox that lists each
Example
[quoted text clipped - 35 lines]

Any help would be greatly appreciated.

--
Message posted via http://www.officekb.com

I did have my "^top" Subs in the worksheet. I have placed the Sub in a Module
and I do not get an error anymore. However, when I click on the "^top" label
nothing happens. What am I doing wrong? Thanks in advance!

Tom Ogilvy wrote:
I ran your code and my event code looked like this:

Sub ComboBox1_Change()
ActiveSheet.DropDowns("Combobox1").ListIndex = 1
Range("A1").Select
End Sub

worked fine for me.

I ran it in a separate routine and it again ran perfectly.
Sub AAA()
ActiveSheet.DropDowns("Combobox1").ListIndex = 1
Range("A1").Select

End Sub

It appears to be some implementation problem on your part. Maybe you have
put it in a sheet module or something - I can't say. But there is nothing
wrong with the suggestion.

When I use this code I get a Run-Time Error '1004'

[quoted text clipped - 13 lines]

Any help would be greatly appreciated.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

Im sorry. An error message did occur when I ran the macro in a module. I dont
know what to do with this. It seems that it should work but its not. Does
anyone have any ideas?

mtm4300 wrote:
I did have my "^top" Subs in the worksheet. I have placed the Sub in a Module
and I do not get an error anymore. However, when I click on the "^top" label
nothing happens. What am I doing wrong? Thanks in advance!

I ran your code and my event code looked like this:

[quoted text clipped - 21 lines]

Any help would be greatly appreciated.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1