Excel- VBA help on macros that size columns and rows
I have a sheet that has 2 sets of buttons.
style#1 buttons:
Sizes the rows and columns and hides unneeded columns
Then second button resets the sheet back to normal
Style#2 buttons:
Hides certain columns
The second button resets the sheet back to normal
The problem is, style one will keep sizing rows and columns every time you push the button.
If you reset style 1 it will also keep resizing and resizing if the button is hit twice.
If the (style #1 or style #2 is pressed) then only the reset button for that should be pushed.
I think if a style button is pressed, the only other button that can be pressed should be the reset button for that style
Once the sheet has been reset then you should not be able to reset more, because it will keep shrinking row heights and column widths
Thanks
-Dummy.xlsm
style#1 buttons:
Sizes the rows and columns and hides unneeded columns
Then second button resets the sheet back to normal
Style#2 buttons:
Hides certain columns
The second button resets the sheet back to normal
The problem is, style one will keep sizing rows and columns every time you push the button.
If you reset style 1 it will also keep resizing and resizing if the button is hit twice.
If the (style #1 or style #2 is pressed) then only the reset button for that should be pushed.
I think if a style button is pressed, the only other button that can be pressed should be the reset button for that style
Once the sheet has been reset then you should not be able to reset more, because it will keep shrinking row heights and column widths
Thanks
-Dummy.xlsm
ASKER
Noire,
Thanks for the comment.
Right now the buttons are shapes that have a macro tied to them.
If I Knew how to disable, I would probably do that.
I have no Idea how to put that into this workbook.
Any help would be appreciated.
Thanks
Chris
Thanks for the comment.
Right now the buttons are shapes that have a macro tied to them.
If I Knew how to disable, I would probably do that.
I have no Idea how to put that into this workbook.
Any help would be appreciated.
Thanks
Chris
One way is to make a variable on module level to remember when the style is set.
And reset on the other button.
That will prevent Style set, or Style reset to run more than once.
See workbook.
--Dummy-1.xlsm
And reset on the other button.
That will prevent Style set, or Style reset to run more than once.
See workbook.
--Dummy-1.xlsm
ASKER
Ejgil,
We are getting close...
After you push style #1 or style #2 the only button that should work is the reset for that style #.
I just pushed Style #2 and then pushed style #1 and the format for the sheet was all messed up. ie wrong column widths.
Thanks
Chris
I have attached Dummy2 it has all the correct column widths...
---Dummy-2.xlsm
We are getting close...
After you push style #1 or style #2 the only button that should work is the reset for that style #.
I just pushed Style #2 and then pushed style #1 and the format for the sheet was all messed up. ie wrong column widths.
Thanks
Chris
I have attached Dummy2 it has all the correct column widths...
---Dummy-2.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Ejgil
You are a wizard.....
Nicely done.
Appreciate your expertize
Can you take a moment and explain how we were able to disable the other buttons that we didn't want to be pressed?
You are a wizard.....
Nicely done.
Appreciate your expertize
Can you take a moment and explain how we were able to disable the other buttons that we didn't want to be pressed?
There are 2 boolean variables, Style1Set and Style2Set.
The 2 macros Batching (Style1) and Packaging Style2) must run when nothing has run, both variables are False.
In each the appropriate button is set to True, allowing only the reset for that to run.
Set up in a table it looks like this.
Macro button Style1Set Style2Set
Style #1 False False
Style #1 Reset True False
Style #2 False False
Style #s Reset False True
The 2 macros Batching (Style1) and Packaging Style2) must run when nothing has run, both variables are False.
In each the appropriate button is set to True, allowing only the reset for that to run.
Set up in a table it looks like this.
Macro button Style1Set Style2Set
Style #1 False False
Style #1 Reset True False
Style #2 False False
Style #s Reset False True
You can enable them again when the relevant 'reset' button has been clicked.