friendly relocatable command buttons

[Elemental]

i just thought of another cool thing that i'd like my excel sheet to do

rather than making copies of buttons all the way down a long page that you can use to quickly jump to sections

i was wondering what kinda code you would use to adjust the "left" and "top" values of a commond button to make it relocate on the same sheet to wherever you jump to.

i'm sure you can imagine the sorta thing im after, it's like one of those annoying java ads on web sites that scrolls down when you do.




i was after more info on that, and how you use a List Box with multiple selections.

say in a list of 5 options linked to however many cells you need to store the selections ( havnt figured that out ), if you would want to use a case argument on the cells, how is that all setup ?

or is there a better way to move buttons and use list boxes ?

 

[Damon Ostrander]

Hi Ele,

Assuming you are using an ActiveX (Controls) button, your code to keep the button at the upper left corner of the pane containing the button could be

Private Sub CommandButton1_Click()
[Q22].Select
CommandButton1.Left = Columns(ActiveWindow.ScrollColumn).Left + 10
CommandButton1.Top = Rows(ActiveWindow.ScrollRow).Top + 10
End Sub

where I used cell Q22 for the sake of example.

I'm not sure I understand the second question, but if you are wanting to have a Listbox in which the list entries represent possible locations to jump to on the worksheet, and you also want to have the listbox always stay with the selected cell you could do something like this:

Private Sub ListBox1_Change()
Dim myCell As Range
Select Case ListBox1.Value
Case "cell B19": Set myCell = [B19]
Case "cell F22": Set myCell = [F22]
Case "cell J5": Set myCell = [J5]
Case "cell Q22": Set myCell = [Q22]
Case "cell B4": Set myCell = [B4]
Case "cell Z26": Set myCell = [Z26]
End Select
myCell.Select
ListBox1.Left = Columns(ActiveWindow.ScrollColumn).Left + 10
ListBox1.Top = Rows(ActiveWindow.ScrollRow).Top + 10
End Sub

In this example the values in quotes are the possible selections in the listbox (ListBox1).

 

[Elemental]

very cool



now i have the buttons wherever i go, so no more scrolling about to find em

yeah, the listbox question was asked using suggestions, instead of what i wanted to do, which is just use a listbox

i have say 5 sheets, and you can choose which single option in the listbox to jump to for viewing (using a case argument for the number stored in the listbox's linked cell)

what i would love to know how to do is to setup a listbox with the 5 options, and then have a button to run some VBA code on only the sheets that were selected (they all must have unique sheet names, and not a constant name plus a number).



im baffled just thinking on how to setup a case arguemnt that im use to, so there must be a better way, could you guys also mention the standard setup for a multi selection listbox, as well as clues on my question ?

how does a normal multi selection listbox work, and how do you alter data only based on the selected options

thanks again.

 

[Elemental]

i was also having some fun setting up locked checkboxes to indicate if things have been completed in excel

any ideas why

=IF(Results!A1:AZ600="",FALSE,TRUE)

works fine, but

=IF(Results!A601:AZ1200="",FALSE,TRUE)

gives a #VALUE error ?

any =IF(Results!A1:AZ600="",FALSE,TRUE)

where A1 is above 7, eg A10 also gives that error

what's up ?


This message was edited by Elemental on 2002-09-11 22:03

 

[Elemental]

stranger still,

any cell above the linked cell for the checkbox ( the one on another sheet called Form ) gives the #VALUE error

???

what does a "Form" sheet with a linked cell of L7 for the checkbox, have to do with getting a #VALUE error when the

=IF(Results!A7:AZ600="",FALSE,TRUE)

is used

got any clues ?

i could just make the linked cells L60000