friendly relocatable command buttons

Elemental

Board Regular
Joined
Jul 13, 2002
Messages
110
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 ?

:)
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Jul 13, 2002
Messages
110
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

Board Regular
Joined
Jul 13, 2002
Messages
110
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

Board Regular
Joined
Jul 13, 2002
Messages
110
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


:)
 

Forum statistics

Threads
1,144,292
Messages
5,723,537
Members
422,502
Latest member
barakgahtan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top