Solved

Excel- VBA help on macros that size columns and rows

Posted on 2016-11-21
7
47 Views
Last Modified: 2016-11-22
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
0
Comment
Question by:chris pike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 41896354
Can't you disable the relevant buttons when one of the 'style' buttons has been clicked and the changes have been made?

You can enable them again when the relevant 'reset' button has been clicked.
0
 

Author Comment

by:chris pike
ID: 41896381
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
0
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 41896628
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
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:chris pike
ID: 41896663
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
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41896702
A few more conditions will do that.
----Dummy-2.xlsm
0
 

Author Closing Comment

by:chris pike
ID: 41897572
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?
0
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 41897608
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
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question