The Placement property does not seem to be working.

[shorepointln]

I have created an active X command button on a worksheet and set its Placement Property to 3 which means "free floating" (Do not move or re-size with cells).

However the button is both moved and re-sized with cells. It seems the value of the placement property makes no difference at all.

I would like the button to stay in the same place on the screen no matter what cells are currently showing.

What am I doing wrong?

Thanks!

 

[GS]

I have created an active X command button on a worksheet and set its

Placement Property to 3 which means "free floating" (Do not move or
re-size with cells).

However the button is both moved and re-sized with cells. It seems
the value of the placement property makes no difference at all.

I would like the button to stay in the same place on the screen no
matter what cells are currently showing.

What am I doing wrong?

Thanks!

Controls on a worksheet will move on you whenever you hide/unhide
rows/cols. This may be cells where the controls are positioned OR it
may be Excel preventing them from shifting 'off' the sheet.

Better to design a cell to look/behave like a button and use an event
in the sheet code to run the macro.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[shorepointln]

I have created an active X command button on a worksheet and set its Placement Property to 3 which means "free floating" (Do not move or re-size with cells).

However the button is both moved and re-sized with cells. It seems the value of the placement property makes no difference at all.

I would like the button to stay in the same place on the screen no matter what cells are currently showing.

What am I doing wrong?

Thanks!

In reply to GS:

The button stays with the cell(s) under it when it is created. If I scroll the cell off screen, the button goes with it. I have not hidden anything.

I want to be able to scroll the worksheet but have the button stay at the same position on the screen.

If I make a cell act like a button, it should & will move when the sheet is scrolled. That is the opposite of what needs to happen, the button should remain unmoved & that is what the Placement property is all about.

The properties list for the button contains the Placement property and it can be set to any valid xlPlacement value so I assume it is supposed to work. However, no matter what I set it to, the button changes sizes size and moves with the underlying cell.

Again I ask, what could I be doing wrong? (Or is Excel just not working correctly)?

 

[Claus Busch]

Hi,

Am Wed, 18 Sep 2013 08:02:43 -0700 (PDT) schrieb (e-mail address removed):

I want to be able to scroll the worksheet but have the button stay at the same position on the screen.

freeze the window below your button.
Or try worksheet event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CommandButton1.Top = ActiveWindow.ScrollRow * 15
End Sub

The 15 you have to suit to your monitor. If you sroll and select a cell,
the button goes down or up.


Regards
Claus B.

 

[GS]

I wasn't sure of your exact scenario but originally intended to suggest
same as Claus did, and also suggest putting your controls as menuitems
so they're not on the sheet at all. Menuitems can be a custom menu or
toolbar, or in XL2007 and later, a custom tab on the ribbon. These
would be created when your file opens, and removed when it closes. My
preference for my projects is that they have there own menus, but I
also put controls above a Freeze Panes row when menus aren't
appropriate or warranted!

Note that the base for control coords is the worksheet, not the screen
as would be for a userform. IOW, when you create controls on worksheets
they become 'children' of their host sheet. The Placement options do
not apply to screen position, but rather aplly to sheet coord position.

Claus' value 15 is what works for his display and so you'd have to find
what works for yours. Problem is it may not work for other users of
your project and so is why I recommend going with custom menus!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Harald Staff]

Yet another idea is to put the button on a modeless userform.
But somewhere on the ribbon/menu system is probably better.
See http://rondebruin.nl/win/section2.htm for everything needed.

Best wishes Harald

 

[GS]

Yet another idea is to put the button on a modeless userform.

But somewhere on the ribbon/menu system is probably better.
See http://rondebruin.nl/win/section2.htm for everything needed.

Best wishes Harald

Thanks, Harald! Yes, I sometimes do this to simulate a 'floating'
toolbar instead of using UI menus. I think, though, it's a bit complex
for this scenario <IMO>!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion