• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

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.
0
nkunapaneni
Asked:
nkunapaneni
  • 7
  • 2
1 Solution
 
tureCommented:
nkunapaneni,

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
0
 
tureCommented:
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(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 VBA code
  for x = 2 to activeworkbook.worksheet.count
    activeworkbook.worksheets(x).visible = xlsheetveryhidden
  next x
end sub

/Ture
0
 
tureCommented:
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\test.xls")

  '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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tureCommented:
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\test.xls")

  '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
  wb.close savechanges:=true

  'Release object variables
  set wb = nothing
  set xl = nothing

end sub

/Ture
0
 
tureCommented:
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\test.xls")

  '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
0
 
tureCommented:
Sorry for triple-posting. I had some problems getting it through. The last comment (before this one) is the correct VB code.

/Ture
0
 
nkunapaneniAuthor Commented:
Thanks alot for help. It really works.

Nalini.
0
 
tureCommented:
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.

http://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
0
 
nkunapaneniAuthor Commented:
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.
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=visualbasic&qid=20172177

Any way thanks for letting me know this.

Nalini.

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now