Hide Excel Sheet using VBA 3

[jode]

Hi,

I am trying to hide an Excel sheet so that when the user loads the system they cannot unhide it using the menu option.
I believe this can be done by hiding the sheet with a parameter of xlveryhidden or something along those lines!!

Anyone any ideas?

Cheers

 

[Nikita6003]

Yup, you're right

use

ThisWorkbook.Worksheets("SheetnameHere").Visible = xlVeryHidden

if the sheet is in the same workbook as the bit of VBA (change

ThisWorkbook

to

Workbooks("NameOf WorkbookHere")

if not)

just insert the sheetname you want to hide - or its index number

Cautionary note - you cannot hide a sheet if it's the last visible sheet in that workbook

Cheers
Nikki

 

[RobBroekhuis]

Yes. You can set it in the VBE environment or programmatically. In VBE, select in the project explorer the excel sheet object you need to make invisible, and change its .visible property to xlSheetVeryHidden. You can also do it in VBA:
activeworkbook.Sheets(1).visible=xlsheetveryhidden
Rob

 

[jode]

Cheers, I have done that now and it works...!

Next problem - I am trying to change the background colour of some cells again using VBA....

Any ideas on that one??

 

[RobBroekhuis]

range("C5&quot.interior.color=vbred
Rob

 

[jode]

Cheers Rob,

Application is almost complete now!

Any ideas what the RGB code is for light grey???

 

[combo]

On my machine is 126322256.
Colour your cell and, when selected, check it with:

[tt]MsgBox ActiveCell.Interior.Color[/tt]

 

[andrew299]

another method
select a cell or range

With Selection.Interior
.ColorIndex = 15 '=light grey
.Pattern = xlSolid
End With


With Selection.Interior
.ColorIndex = 48 '= slightly darker grey
.Pattern = xlSolid
End With

With Selection.Interior
.ColorIndex = 16 '=darker grey still
.Pattern = xlSolid
End With

Andrew299 It may have hit every branch on its way out of the ugly tree, but hey! It works. (sometimes)

 

[andrew299]

Or if you want custom colours

ActiveWorkbook.Colors(1) = RGB(215, 215, 215)
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
End Sub

sets the top right hand corner colour (colour 1) to the colour specified by the nos. 215,215,215 which say how much RGB is in each colour.
Be careful though as if you change this again in the sam emacro the first cell you changed coulouyr will also change to the new colour- it you see what I mean
Andrew299 It may have hit every branch on its way out of the ugly tree, but hey! It works. (sometimes)