Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to hide excel worksheet from VB

Posted on 2001-08-22
9
Medium Priority
?
809 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 22

Accepted Solution

by:
ture earned 200 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
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…
Suggested Courses

610 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