Solved

How to hide excel worksheet from VB

Posted on 2001-08-22
9
798 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
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!

 
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
 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MsgBox 2 61
MS Access Search and Replace Using VBA 6 92
VB6 - Convert HH:MM into Decimal 8 70
VBA - If Bookmark = "XXBOOKMARKXX" then 15 52
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

697 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