Excel 2010 make visible a hidden worksheet

B

brutium

Joined
Feb 28, 2012
Messages
5
Reaction score
0
Hello everyone. This is my VERY first post. I am new at Excel VBA and I am doing my best to learn...
I have created, with the help of some colleagues, a workbook in order to calculate marks for a class. I have several sheets in the book. One of the sheets is supposed to be used to input marks which are then calculated, averaged and placed in a table in a different sheet.

:(My problem is that I would like to call up the worksheet in which I need to input the marks. Everytime I click on the button with the macro to make my sheet visible, I get a Run-time error '9' Subscript out of range. My macro says::confused:

Sub MarkEntryVisible()
Worksheets("Mark Entry").Visible = True
Worksheets("Mark Entry").Activate

End Sub

What am I doing wrong? How can I resolve the problem?

Any help would be greatly appreciated.

Thank you
 
brutium,

Try this:

Sub MarkEntryVisible()
sheets("Mark Entry").Visible = True
sheets("Mark Entry").Activate

End Sub

Stoneboysteve
 
Hello Stoneboysteve,
Thank you for your reply. I did try your suggestion, but no success. I believe that this problem is caused by the fact that the "Mark Entry" sheet was an after-thought. We have created a workbook where the teachers would be able to create worksheets automatically from a list and these worksheets are based on a template. The after-thought was that the marks for each e.g. TEST would have to come from 4 components, and this is the reason for the "new" sheet which I called "Mark Entry". My problem is that when I click on the button that launches the macro to create the various sheets, the "Mark Entry" sheet is automatically deleted, and so my problem....

Part of the script that does all this is as follows:


'Determine if any sheets need to be added and add them if so.

oShtCt = ThisWorkbook.Sheets.Count 'Original sheet count
masterSht.Visible = xlSheetVisible
For Each c In toolsRng
If Not IsEmpty(c) Then
If Not SheetExists(c.Value) Then
x = 0
masterSht.Copy _
after:=Sheets(Sheets.Count)
ActiveSheet.Name = _
c.Value
End If
End If
Next c
mShtCt = ThisWorkbook.Sheets.Count
addedShtCt = mShtCt - oShtCt

'Take inventory of all sheets currently in the workbook

ReDim shtNames(1 To mShtCt)
For i = 1 To mShtCt
shtNames(i) = ThisWorkbook.Sheets(i).Name
Next i

'Remove any sheets other than the 3 basic sheets that are not on the current Eval Tool list.

deletedShtCt = 0
For i = 1 To mShtCt
If shtNames(i) <> toolsSht.Name And shtNames(i) <> classSht.Name _
And shtNames(i) <> masterSht.Name Then
On Error Resume Next
Z = WorksheetFunction.Match(shtNames(i), toolsRng, 0)
If Err.Number <> 0 Then
Sheets(shtNames(i)).Delete
deletedShtCt = deletedShtCt + 1
On Error GoTo 0
End If
End If
Next i


What I would like to do is modify the script so that I would be able to Hide the "Mark Entry" sheet and call it back when I click on a button that launches the macro.

Can you help?

Thank you
 
Last edited:
Back
Top