Vba to control of visibility, position, and scroll bars for each of several worksheets in the same workbook
How, for several worksheets (ws) in one workbook, do I use vba to:
- make set each ws, independently, as either visible or hidden
- for each ws set its scroll bars (on or off)
- for each ws set the .top .width .left .height values for each, within the same workbook window.
Thanks
- make set each ws, independently, as either visible or hidden
- for each ws set its scroll bars (on or off)
- for each ws set the .top .width .left .height values for each, within the same workbook window.
Thanks
ASKER
Thanks.
Top priority is - when the workbook is opened.
However, not far down the road, I'll need to show some Excel sheets (and hide others) in particular contexts.
So I'd be glad to take it step at a time with you now... improving my understanding of the relevant bits of the object model and its control would be timely.
Kelvin
Top priority is - when the workbook is opened.
However, not far down the road, I'll need to show some Excel sheets (and hide others) in particular contexts.
So I'd be glad to take it step at a time with you now... improving my understanding of the relevant bits of the object model and its control would be timely.
Kelvin
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
No problem.
Have you got an existing workbook you would like the code added to, & that you can post within a following comment, or would you just like to see an example working in a sample workbook?
Have you got an existing workbook you would like the code added to, & that you can post within a following comment, or would you just like to see an example working in a sample workbook?
ASKER
My existing workbook drives a visio application (superior ribbonX in excel vs visio, plus added spread sheet support for creating lists of visio output drawings). So I'd rather ask for a sample file, and have a first shot at transferring the methods.
Thanks
Thanks
ASKER
Hi FanPages,
Congratulations!!.. (I read: "08 Sep 2013: Appointed Community Volunteer: Topic Advisor (Microsoft Excel)"). Your latest appointments contributes to a fantastic service, which I've enjoyed for ~ 2yrs.
Given your additional load, can you indicate your availability for this Q? Then I can schedule my coding around the 'block' I have on viewing Excel sheets. Somehow (and I regret I cant quite see how) I've lost sight of all except one excel sheet, and I seek the most effective methods to force the others to 'reveal themselves'.
If those methods fail, I'll have to migrate the whole excel component of the project into a fresh excel file to identify the killer code or whatever.
Cordially,
Kelvin
Congratulations!!.. (I read: "08 Sep 2013: Appointed Community Volunteer: Topic Advisor (Microsoft Excel)"). Your latest appointments contributes to a fantastic service, which I've enjoyed for ~ 2yrs.
Given your additional load, can you indicate your availability for this Q? Then I can schedule my coding around the 'block' I have on viewing Excel sheets. Somehow (and I regret I cant quite see how) I've lost sight of all except one excel sheet, and I seek the most effective methods to force the others to 'reveal themselves'.
If those methods fail, I'll have to migrate the whole excel component of the project into a fresh excel file to identify the killer code or whatever.
Cordially,
Kelvin
Hi Kelvin,
Thanks for your well wishes.
Your question is actually on my pile of "to look at" today. That & three others. I'm working on one of the other three now, but I can bump your question up the 'priority stack' thereafter.
I'll come back to you later today in any respect, but I do appreciate the reminder all the same :)
BFN,
fp.
Thanks for your well wishes.
Your question is actually on my pile of "to look at" today. That & three others. I'm working on one of the other three now, but I can bump your question up the 'priority stack' thereafter.
I'll come back to you later today in any respect, but I do appreciate the reminder all the same :)
BFN,
fp.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi fanpages,
Many thanks for the attached, which I was unable to attend to yesterday.
I'll get right into it now, and report back quickly.
Appreciatively
Kelvin
Many thanks for the attached, which I was unable to attend to yesterday.
I'll get right into it now, and report back quickly.
Appreciatively
Kelvin
No problem at all, Kelvin.
Thanks for letting me know.
Thanks for letting me know.
ASKER
Hi fanpages,
Many thanks for a comprehensive solution.
One question:
- When you write; dblActiveWindow_Height = 50# , what does the '#' do?
Thanks again
Kelvin
Many thanks for a comprehensive solution.
One question:
- When you write; dblActiveWindow_Height = 50# , what does the '#' do?
Thanks again
Kelvin
Hi,
The # symbol (or "type declaration character") indicates a Double data type.
For example, declaring...
Dim dblActiveWindow_Height#
is the same as:
Dim dblActiveWindow_Height As Double
When used on the line you indicated, I am explicitly making the value of 50 a Double-precision floating-point number so that the execution of the code is quicker; in that if I just typed 50 (without the # suffix), the value would have to be converted to a Double data type by the Visual Basic for Applications code interpreter at run-time to 'load' into the variable (dblActiveWindow_Height).
I could have also typed:
dblActiveWindow_Height = 50.0
If you enter this, you will see it be automatically changed to what I did originally type:
dblActiveWindow_Height = 50#
In this single instance, this may not appear to be a great improvement but if, for example, that single line was within a loop (of hundreds, or thousands) of iterations, or there were multiple variables being set at once, &/or a If statement comparing a variable against a known value, then you may well notice a worthwhile speed improvement if the typed 50 (that would default to an Integer data type) had to be converted to a Double data type every time the respective line of code was executed (many times over in a loop, for example).
This is a practice I adopted from my very early usage of Microsoft Visual Basic (for Windows) back in 1991, when PCs had less Random Access Memory, compounded with the fact that the speed of the Central Processing Units were much slower than the processors we use today.
Finding "shortcuts" like this to improve the speed of execution was, in some cases, the difference between code execution completing in a reasonable time, or even completing at all (instead of seeing an "Out of Memory" error)!
So, in some respects, I do this because I have always done this, & it is not strictly necessary (now). It does no harm other than, perhaps, confusing those unfamiliar with it. You are not the first to query my code in this way.
If you read any of my other code listings (provided in other questions) you will see I also do this for other data types, for example:
Dim lngValue As Long
lngValue = 0&
Dim sngValue As Single
sngValue = 0!
Dim curValue As Currency
curValue = 0@
You may not see this approach (as often), as the default data type without a type declaration character as a suffix to an explicit value, is an Integer in any respect:
Dim intValue As Integer
intValue = 0%
is equivalent to
Dim intValue As Integer
intValue = 0
BFN,
fp.
The # symbol (or "type declaration character") indicates a Double data type.
For example, declaring...
Dim dblActiveWindow_Height#
is the same as:
Dim dblActiveWindow_Height As Double
When used on the line you indicated, I am explicitly making the value of 50 a Double-precision floating-point number so that the execution of the code is quicker; in that if I just typed 50 (without the # suffix), the value would have to be converted to a Double data type by the Visual Basic for Applications code interpreter at run-time to 'load' into the variable (dblActiveWindow_Height).
I could have also typed:
dblActiveWindow_Height = 50.0
If you enter this, you will see it be automatically changed to what I did originally type:
dblActiveWindow_Height = 50#
In this single instance, this may not appear to be a great improvement but if, for example, that single line was within a loop (of hundreds, or thousands) of iterations, or there were multiple variables being set at once, &/or a If statement comparing a variable against a known value, then you may well notice a worthwhile speed improvement if the typed 50 (that would default to an Integer data type) had to be converted to a Double data type every time the respective line of code was executed (many times over in a loop, for example).
This is a practice I adopted from my very early usage of Microsoft Visual Basic (for Windows) back in 1991, when PCs had less Random Access Memory, compounded with the fact that the speed of the Central Processing Units were much slower than the processors we use today.
Finding "shortcuts" like this to improve the speed of execution was, in some cases, the difference between code execution completing in a reasonable time, or even completing at all (instead of seeing an "Out of Memory" error)!
So, in some respects, I do this because I have always done this, & it is not strictly necessary (now). It does no harm other than, perhaps, confusing those unfamiliar with it. You are not the first to query my code in this way.
If you read any of my other code listings (provided in other questions) you will see I also do this for other data types, for example:
Dim lngValue As Long
lngValue = 0&
Dim sngValue As Single
sngValue = 0!
Dim curValue As Currency
curValue = 0@
You may not see this approach (as often), as the default data type without a type declaration character as a suffix to an explicit value, is an Integer in any respect:
Dim intValue As Integer
intValue = 0%
is equivalent to
Dim intValue As Integer
intValue = 0
BFN,
fp.
Do you need these attribute changes to occur when each worksheet is active (selected), or do you wish to set all the attributes at one point in time (prior to any of the worksheets being active; for example, whilst the workbook is opened, or following another event such as clicking a button intended to specifically change the attributes)?
Thanks for your clarification.
BFN,
fp.