Solved

How to hide excel worksheet from VB

Posted on 2001-08-22
9
791 Views
Last Modified: 2012-05-04
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
Comment
Question by:nkunapaneni
  • 7
  • 2
9 Comments
 
LVL 22

Expert Comment

by:ture
ID: 6413288
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
 
LVL 22

Expert Comment

by:ture
ID: 6413327
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
 
LVL 22

Expert Comment

by:ture
ID: 6413385
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
 
LVL 22

Accepted Solution

by:
ture earned 50 total points
ID: 6413387
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 22

Expert Comment

by:ture
ID: 6413393
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
 
LVL 22

Expert Comment

by:ture
ID: 6413430
Sorry for triple-posting. I had some problems getting it through. The last comment (before this one) is the correct VB code.

/Ture
0
 

Author Comment

by:nkunapaneni
ID: 6413488
Thanks alot for help. It really works.

Nalini.
0
 
LVL 22

Expert Comment

by:ture
ID: 6413615
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
 

Author Comment

by:nkunapaneni
ID: 6441738
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now