nkunapaneni
asked on
How to hide excel worksheet from VB
I want to show only first worksheet of excel file and hide rest of the sheets if any existing from Visual basic appication. ANd also want to hide the formulas in the worksheet through the application.
I would appreciate any help.
Thanks.
I would appreciate any help.
Thanks.
nkunapaneni,
Here is the VBA version:
sub hidestuff()
'Declare a variable to use when looping through the worksheets
dim x as integer
'Make sure that the first worksheet is visible,
'hide all formulas of the sheet and protect the
'sheet with the password "carrot"
with activeworkbook.worksheets(
.visible = true
.cells.formulahidden = true
.protect password := "carrot"
end with
'Hide all worksheets except first sheet.
'Setting the visible property to xlsheetveryhidden makes it
'impossible to unhide the sheets except from VBA code
for x = 2 to activeworkbook.worksheet.c
activeworkbook.worksheets(
next x
end sub
/Ture
Here is the VBA version:
sub hidestuff()
'Declare a variable to use when looping through the worksheets
dim x as integer
'Make sure that the first worksheet is visible,
'hide all formulas of the sheet and protect the
'sheet with the password "carrot"
with activeworkbook.worksheets(
.visible = true
.cells.formulahidden = true
.protect password := "carrot"
end with
'Hide all worksheets except first sheet.
'Setting the visible property to xlsheetveryhidden makes it
'impossible to unhide the sheets except from VBA code
for x = 2 to activeworkbook.worksheet.c
activeworkbook.worksheets(
next x
end sub
/Ture
nkunapaneni,
Here's the VB version.
You must set a reference to Microsoft Excel x.x Object library first.
sub hidestuffVB()
'Declare variables
dim xl as excel.application
dim wb as excel.workbook
dim x as integer
'Start Excel and open a workbook
set xl = new excel.application
set wb = xl.workbooks.open("c:\test
'Make sure that the first worksheet is visible,
'hide all formulas of the sheet and protect the
'sheet with the password "carrot"
with wb.worksheets(1)
.visible = true
.cells.formulahidden = true
.protect password := "carrot"
end with
'Hide all worksheets except first sheet.
'Setting the visible property to xlsheetveryhidden makes it
'impossible to unhide the sheets except from VB/VBA code
for x = 2 to wb.worksheet.count
wb.worksheets(x).visible = xlsheetveryhidden
next x
'Close and save workbook, quit Excel
wb.close savechanges:=true
xl.quit
'Release object variables
set wb = nothing
set xl = nothing
end sub
/Ture
Here's the VB version.
You must set a reference to Microsoft Excel x.x Object library first.
sub hidestuffVB()
'Declare variables
dim xl as excel.application
dim wb as excel.workbook
dim x as integer
'Start Excel and open a workbook
set xl = new excel.application
set wb = xl.workbooks.open("c:\test
'Make sure that the first worksheet is visible,
'hide all formulas of the sheet and protect the
'sheet with the password "carrot"
with wb.worksheets(1)
.visible = true
.cells.formulahidden = true
.protect password := "carrot"
end with
'Hide all worksheets except first sheet.
'Setting the visible property to xlsheetveryhidden makes it
'impossible to unhide the sheets except from VB/VBA code
for x = 2 to wb.worksheet.count
wb.worksheets(x).visible = xlsheetveryhidden
next x
'Close and save workbook, quit Excel
wb.close savechanges:=true
xl.quit
'Release object variables
set wb = nothing
set xl = nothing
end sub
/Ture
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
nkunapaneni,
Here's the VB version.
You must set a reference to Microsoft Excel x.x Object library first.
sub hidestuffVB()
'Declare variables
dim xl as excel.application
dim wb as excel.workbook
dim x as integer
'Start Excel and open a workbook
set xl = new excel.application
set wb = xl.workbooks.open("c:\test
'Make sure that the first worksheet is visible,
'hide all formulas of the sheet and protect the
'sheet with the password "carrot"
with wb.worksheets(1)
.visible = true
.cells.formulahidden = true
.protect password := "carrot"
end with
'Hide all worksheets except first sheet.
'Setting the visible property to xlsheetveryhidden makes it
'impossible to unhide the sheets except from VB/VBA code
for x = 2 to wb.worksheet.count
wb.worksheets(x).visible = xlsheetveryhidden
next x
'Close and save workbook, quit Excel
wb.close savechanges:=true
xl.quit
'Release object variables
set wb = nothing
set xl = nothing
end sub
/Ture
Here's the VB version.
You must set a reference to Microsoft Excel x.x Object library first.
sub hidestuffVB()
'Declare variables
dim xl as excel.application
dim wb as excel.workbook
dim x as integer
'Start Excel and open a workbook
set xl = new excel.application
set wb = xl.workbooks.open("c:\test
'Make sure that the first worksheet is visible,
'hide all formulas of the sheet and protect the
'sheet with the password "carrot"
with wb.worksheets(1)
.visible = true
.cells.formulahidden = true
.protect password := "carrot"
end with
'Hide all worksheets except first sheet.
'Setting the visible property to xlsheetveryhidden makes it
'impossible to unhide the sheets except from VB/VBA code
for x = 2 to wb.worksheet.count
wb.worksheets(x).visible = xlsheetveryhidden
next x
'Close and save workbook, quit Excel
wb.close savechanges:=true
xl.quit
'Release object variables
set wb = nothing
set xl = nothing
end sub
/Ture
Sorry for triple-posting. I had some problems getting it through. The last comment (before this one) is the correct VB code.
/Ture
/Ture
ASKER
Thanks alot for help. It really works.
Nalini.
Nalini.
You are very welcome, Nalini! I'm glad that I could help you.
You gave me a "B" grade. Was the information I provided what you had hoped for, or did you miss something in my solution?
Please don't be offended by my comment below - I am not complaining, I only want to inform you about how the grading system is used. It can really can be a bit confusing.
The wording of the grades (Excellent, Good, Average) do not really match the way the grades are commonly used. I have started a discussion about this in the customer support forum.
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20171038
Below is a comment from that discussion, written by "modder" who is an Experts Exchange moderator:
>When you're grading an expert's answer it is important to
>remember that the grade you award to a question
>is a token of appreciation, not a "school" grade.
>
>In general, I would advise to give an "A" unless you have
>a good reason to grade less, for example,
>if you asked for more information and you didn't get it,
>or the expert only gave you a starting point
>
>and you still had to do most of the grunge work yourself.
>
>A "C" grade is particularly unacceptable if the person
>simply posted their suggestion as a comment.
>After all, you are not obliged to accept a comment as the
>answer.
>
>Whatever the case may be, if you're considering giving
>a "B" or a "C", please tell the expert beforehand,
>so that they have a chance to work on making their answer
>worth that "A".
>
>Another thing you might want to consider is that a lot of
>our top experts check an asker's grading record.
>
>If they see that an asker habitually grades questions
>with "B"s or even "C"s they would be very reluctant
>to give their help.
>
>Anyway, I'm sure it was just a mishap. It certainly
>doesn't help that the EE settings are such that
>the "B" grade is the default. If you want to re-consider
>some of the grades you have given, just post
>the URLs of the questions here, and I will improve the
>grades on your behalf. This will also be reflected
>in your grading record.
/Ture
You gave me a "B" grade. Was the information I provided what you had hoped for, or did you miss something in my solution?
Please don't be offended by my comment below - I am not complaining, I only want to inform you about how the grading system is used. It can really can be a bit confusing.
The wording of the grades (Excellent, Good, Average) do not really match the way the grades are commonly used. I have started a discussion about this in the customer support forum.
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20171038
Below is a comment from that discussion, written by "modder" who is an Experts Exchange moderator:
>When you're grading an expert's answer it is important to
>remember that the grade you award to a question
>is a token of appreciation, not a "school" grade.
>
>In general, I would advise to give an "A" unless you have
>a good reason to grade less, for example,
>if you asked for more information and you didn't get it,
>or the expert only gave you a starting point
>
>and you still had to do most of the grunge work yourself.
>
>A "C" grade is particularly unacceptable if the person
>simply posted their suggestion as a comment.
>After all, you are not obliged to accept a comment as the
>answer.
>
>Whatever the case may be, if you're considering giving
>a "B" or a "C", please tell the expert beforehand,
>so that they have a chance to work on making their answer
>worth that "A".
>
>Another thing you might want to consider is that a lot of
>our top experts check an asker's grading record.
>
>If they see that an asker habitually grades questions
>with "B"s or even "C"s they would be very reluctant
>to give their help.
>
>Anyway, I'm sure it was just a mishap. It certainly
>doesn't help that the EE settings are such that
>the "B" grade is the default. If you want to re-consider
>some of the grades you have given, just post
>the URLs of the questions here, and I will improve the
>grades on your behalf. This will also be reflected
>in your grading record.
/Ture
ASKER
HI Ture,
I am sorry about this. I don't have much idea on how the grading system works here in EE.
I would like to reconsider the grade i have given and change to 'A'. IS it be possible to do? This is the url of the question.
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=visualbasic&qid=20172177
Any way thanks for letting me know this.
Nalini.
I am sorry about this. I don't have much idea on how the grading system works here in EE.
I would like to reconsider the grade i have given and change to 'A'. IS it be possible to do? This is the url of the question.
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=visualbasic&qid=20172177
Any way thanks for letting me know this.
Nalini.
Do you mean VBA (Visual Basic for Applications, the VB version built into Excel) or VB (Visual Basic - a separate development product)?
Ture Magnusson
Karlstad, Sweden