+ Reply to Thread
Results 1 to 11 of 11

Erratic User Form Command Button behavior

  1. #1
    Registered User
    Join Date
    11-24-2013
    Location
    florida
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    29

    Erratic User Form Command Button behavior

    I have a single worksheet that contains a condensed report area beginning in column BA and a data entry area beginning on Column A.
    There is a user form command button on each that runs a macro to let user flip between the two. Column A in the data entry area is a frozen column.

    After clicking the command button on the data entry area to go to the report area the original command button on the report changes from standard button appearance to just text with an oval line around it. No button outline or background fill. When you click on it, it suddenly shrinks to a tiny button in the upper left corner of its cell. The size parameters also change from normal to Height .04" and Width .29". This behavior is consistent even after changing the button text to one word vs. the normal 3.

    On the other hand if I select the shrunken button and expand it back to its original size, the original (normal) button appearance returns and the button functions normally. While I might expect odd behavior from memory problems, this behavior remains even after rebooting. I have tested this on two different PCs with the same result.

    Varying the properties does not seem to have an effect. The relevant properties are:
    Print Object
    Don't move or size with cells
    and Alignment: "Automatic Size" is not checked
    Size: "Lock Aspect Ratio" is not checked
    Margins: "Automatic" is checked

    I have similar worksheets in the same workbook and the result is the same on each. Strange -Can anyone help?
    -------------------------------------------------------------------------------------------------------------------
    12/30/2013
    SOLVED!!!!!
    Thanks to everyone for your guidance. Here's what I have found after further tests...
    1. The resizing issue occurs on both Form command buttons and Active X.
    2. The resizing of command buttons will only occur after printing a selection from the worksheet. It will then resize (shrink) only after pageup/down or several clicks on various portions of the same worksheet.
    3. Confirmed that this is an apparent bug - not a function of code.
    4. Solution: I switched from a command button and inserted a rectangle shape instead. Set its properties to look like a command button and attached the proper macros to it. This completely cured the problem and eliminated the need to have a special macro to temporarily resize the button. Everything is now nice and clean and stable. Repeated for the 3 buttons on the worksheet.
    Again thanks to all !!
    Last edited by RonRich; 12-30-2013 at 11:29 AM. Reason: Solution described

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Erratic User Form Command Button behavior

    Suggest you post a sample workbook that exhibits the problem.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Erratic User Form Command Button behavior

    I have seen this behavior when using external monitors connected to a laptop with a different resolution than the native laptop resolution. However, when I bought my new laptop, I no longer have this problem when using the same external monitors.

    I have also heard of other people experiencing a similar problem with ActiveX controls. I do not think this is related to memory at all, and more likely related to display drivers. It seems to be a bug rather than any particular configuration/feature of Excel. I am not a Windows programmer so I don't have any insight as to whether the bug is in Excel, display drivers, Windows, or somewhere else.

    All of the fixes I have heard of are workarounds, not solutions. For example, I have written a Sub that sets the size and position of my controls so I can run it to get everything cleaned up again.

    A shot in the dark would be to ensure that your graphics adapter driver is up to date.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Erratic User Form Command Button behavior

    Here are Google search results on this problem.

    https://www.google.com/search?q=exce...sm=93&ie=UTF-8

  5. #5
    Registered User
    Join Date
    11-24-2013
    Location
    florida
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    29

    Re: Erratic User Form Command Button behavior

    6String:
    You put me on the right track. After several tests on 2 PCs, etc. I have determined that the command button resizing problem can only be cured by resizing the button in some manner and it then will restore back to correct size. I have done this by adding lines in my macros which resize the button row slightly and then setting it back to the correct height again. This works.

    However, if you then move around the worksheet clicking on several items, the buttons may suddenly resize back to their tiny size and need to be corrected again. As long as there is not too much activity, everything remains normal. Since this may not be how a user operates I think a further sizing routine may be necessary. Perhaps the Sub routine you mentioned could do the job. Would you pass it along with any further suggestions please?

    I do appreciate your taking the time to help with this.
    Last edited by RonRich; 12-29-2013 at 11:06 AM.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Erratic User Form Command Button behavior

    Here is on quick example. In this form all my buttons are the same size. The location isn't a problem, just the size. This finds all the buttons and sets the size. If buttons are different sizes then you have to code each one individually.

    Please Login or Register  to view this content.

  7. #7
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Erratic User Form Command Button behavior

    I suggest you may try Forms buttons instead of ActiveX if you will have them on worksheets.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  8. #8
    Registered User
    Join Date
    11-24-2013
    Location
    florida
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    29

    Re: Erratic User Form Command Button behavior

    Thanks Izandol. However, the buttons are already the Forms command buttons not Active X. Inserting a couple steps at the end of the button macro to resize the button row and then return to normal size does keep the button at the correct size. Now its only after clicking around the worksheet that it may then shrink. THAT is the problem that remains.
    Last edited by RonRich; 12-29-2013 at 06:36 PM.

  9. #9
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Erratic User Form Command Button behavior

    Your title says user form command button, which is ActiveX. It is uncommon for Forms button to behave so. Code provided by 6StringJazzer is for ActiveX also and will not work for Forms buttons.

  10. #10
    Registered User
    Join Date
    11-24-2013
    Location
    florida
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    29

    Re: Erratic User Form Command Button behavior

    Thanks Izandol. You are right - I should have said just Forms button. They are not active X. I have now converted one button to Active X and am testing.

  11. #11
    Registered User
    Join Date
    11-24-2013
    Location
    florida
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    29

    Re: Erratic User Form Command Button behavior

    Again thanks 6String for your help. Solution noted as addendum on first post but basically confirmed it is a bug and switched to a shape instead of a command button.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Confirm Command button on user form using VBA
    By coach.32 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2013, 05:56 AM
  2. Want to add disply and edit command button in user form- :(
    By sinha.riteshabap in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2013, 03:21 AM
  3. Help to creat user form with command button
    By sheree08 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-28-2013, 04:10 PM
  4. User form RTE 13 when opening with a command button on a worksheet
    By iaingidley in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-08-2011, 11:50 AM
  5. Command Button on User form for Printing
    By Anthony Slater in forum Excel General
    Replies: 2
    Last Post: 09-07-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1