Excel - User-Created Buttons Resizing and Moving 1

[MattGreer]

This is related to this thread, which was closed. The buttons will resize themselves when I open the file and also resize and move when I click them during use. It's completely random, it seems.

Really frustrated by this as I have to muck about with the buttons when they change. It's really random. I wish I could identify the cause. Attached is a screenshot of the buttons which were recently nicely formatted and lined up.

Any help is much appreciated.



Thanks!!


Matt

 

[dhookom]

it looks like they resize based on row height.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

You might be right about the row height. None of the macros change the row heights specifically, but the table gets re-sorted and that will change the heights of the rows.

Is there any way to separate the buttons from being affected by the row height? I've tried to modify any settings that would cause the buttons to re-size.

I attached a video of what happens when I click a button with the table-formatting/re-sorting changes in the same row.


Thanks!!


Matt

 

[combo]

It seems that you have form buttons. Right-click the button, display format dialog, set behaviour in properties tab (For activex buttons switch to design mode first and continue the same way. By default form button resize with cells, activex button preserves its size.).

combo

 

[MattGreer]

I have all the buttons set as "Don't move or size with cells". Is that what you are talking about?

I've confirmed that they are all ActiveX buttons. When I place them nothing happens, as opposed to when I place a Form Control button and it asks me to assign a macro. It's never done that to me before.

Thanks!!


Matt

 

[combo]

Yes.
The shared video does not show any moving or resizing controls, except of temporary jump when the macro is executed. You may try to play with turning off ScreenUpdating property.
You can also separate control area and data range. Controls reside on the drawing layer. There is no perfect cooperation between drawing layer and data grid, some settings are not stored. I did a quick test (excel 2016):
1) add activex command button, top left corner as cell C3,
2) hide columns A:C (command button moves to the left edge, above column D),
3) select all, unhide hidden columns - command button still is in C3,
4) hide A:C again (step 2),
5) save and reopen workbook,
6) unhide hidden columns (step 3) - command button stays at the edge of worksheet, in column A.

combo

 

[MattGreer]

Yes, the video didn't show the buttons jumping around too much. It does happen. It's so random it's hard to catch. There have been times when I click on the button, and the button jumps up to just out of reach of the mouse, so I can't actually click it. It's the strangest and most frustrating thing! I was thinking about pulling the nuclear option and creating a new workbook and copying what I need over to the new file. This spreadsheet has been around for a while, since Excel 2010 I think.

I think, optimally, it would be best to simply create a menu with these couple functions built into it so I'm not having to freeze panes and scroll around to get back to the buttons. Problem is I'm not sure how to go about creating a custom menu for just one spreadsheet. I wouldn't want that menu to show up in every spreadsheet I open.

I have a copy of Walkenbach's Power Programming for Excel 2013. I'd be shocked if there wasn't something in there about creating a custom menu through VBA whenever you open a workbook. But, is that how it's done?




Thanks!!


Matt

 

[Andrzejek]

Can you create a user Form in VBA, place all your buttons on the Form, and give the form to the user?
Or do your buttons have to be on the worksheet?


---- Andy

There is a great need for a sarcasm font.

 

[Deniall]

Matt

Your re-raising of this problem has got me thinking about it again, and doing a bit of experimentation.[ ] The "solution" I offered last time (October last year) is only a partial one, in that it merely gives users a way to fix the intermittent problem AFTER they have noticed that it has occurred.[ ] My current thought is that, if we cannot stop the problem from arising, it might be possible to fix things BEFORE it shows itself to the user.[ ] This could work only if the problem is triggered either by the initiating of the button-driven actions or during them.[ ] If it is triggered after the actions have been completed then we are still up that creek without that paddle.

Exploring this thought required me to be able to produce the problem in the first place. Muphry's Law got in the way here, because for several days I couldn't get any of my intermittently-moving command buttons to move.[ ] I work on a laptop computer, and most of my work is done at my desk, where I use a larger "second screen".[ ] Eventually, acting on a weird hunch, I switched mid-testing to the smaller native screen.[ ] Bingo.[ ] I could get the problem to occur once or twice (still intermittently).[ ] Not consistently, but sufficiently often for me to explore my idea to the point where I had proven to myself that when the problem arises it has arisen before the button-driven actions have fully completed.**

So I added some extra code to the end of the VBA routine that is activated when the command button is clicked upon.[ ] See below.

Private Sub GenerateScenarios_Click()
'
'  Perform the requested simulations when the "GenerateScenarios" button is pushed.
'
'  All the hard work is done in a standard subroutine.
'  (But it could just as easily be done here.)
'
Call Run_Simulations
'
'  Experimental stuff to explore the "button moving" problem.
'
Dim TempH As Double, TempL As Double, TempT As Double, TempW As Double
With Sheets("Parking_Analysis").Shapes("GenerateScenarios")
    TempH = .Height
    TempL = .Left
    TempT = .Top
    TempW = .Width
    If TempH <> 60.75 Or TempL <> 375 Or TempT <> 658.5 Or TempW <> 114.75 Then
        .Height = 60.75
        .Left = 375
        .Top = 658.5
        .Width = 114.75
        MsgBox "MOVED from:" & Chr(13) & _
            "      Height = " & TempH & Chr(13) & _
            "      Left  =  " & TempL & Chr(13) & _
            "      Top  =  " & TempT & Chr(13) & _
            "      Width =  " & TempW & Chr(13) & _
            "TO:" & Chr(13) & _
            "      Height = " & .Height & Chr(13) & _
            "      Left  =  " & .Left & Chr(13) & _
            "      Top  =  " & .Top & Chr(13) & _
            "      Width =  " & .Width
    End If
End With
'
End Sub

This includes a lot of actions that are there only for diagnostic purposes, actions I intended to prune out once I had categorically confirmed to myself that everything was working as I hoped it would.[ ] At its minimalist all that is needed is:

Private Sub GenerateScenarios_Click()
'
'  Perform the requested simulations when the "GenerateScenarios" button is pushed.
'
'  All the hard work is done in a standard subroutine.
'  (But it could just as easily be done here.)
'
Call Run_Simulations
'
'  Put the damn button back in its correct place.
'
With Sheets("Parking_Analysis").Shapes("GenerateScenarios")
    .Height = 60.75
    .Left = 375
    .Top = 658.5
    .Width = 114.75
End With
'
End Sub

But Prof Muphry intervened again, and since I added this code in its first (full) form I have not been able to get the misbehaviour to recur.[ ] I am quietly confident that this approach will solve the problem, and will implement it in my afflicted worksheets and see how it goes.[ ] But until Muphry backs off a bit some miniscule doubt remains.

Perhaps you could try it as well, and see whether you can come up with definitive proof.

**[ ] A footnote.[ ] As described above, I was able to trigger the problem (occasionally and seemingly randomly) by changing between screens of different resolutions[ ] Maybe, in the hands of someone massively more knowledgeable than me, this fact could shed some light on WHY and HOW the problem arises in the first place.