Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

xlVeryHidden

Why will:

Sheets("WhatEver").Visible = xlVeryHidden

work in a module or sheet code page, but not in the ThisWorkBook module within WorkBook_Open() or WorkBook_BeforeClose()? Note: I have at least one sheet visible at all times.

I did a cut & paste of the exact same code from the Sheet1 module to the ThisWorkBook module under both of the above (at different tests) and it always fails in an error '1004': "Unable to set the Visible property of the WorkSheet class."

Is there a way to do so?


Post your answer or comment

comments powered by Disqus
Mi intención era Imprimir un rango a traves de codigo:
rng.printout,...., preview:= true.
La hoja en la que se encuentra rng se encuentra en estado visible = xlveryhidden

pero la Macro corria y el preview no aparecía

Mi solucion:
antes de imprimir

Application.ScreenUpdating = false

rng.parent.visible = true

instrucciones

rng.parent.visible = xlveryhidden

application.ScreenUpdating = True

Ahora pregunto:
¿Es incompatible el Metodo PrintOut con un rango contenido en una hoja oculta? ¿Hay alguna manera de evitar mi "solucion"?

GALILEOGALI

Hi All:

I am trying to get a code that will Hide ALL sheets (xlVeryHidden) except for "Meeting Minutes" and "Index"

I want to do it 2 ways.

1) User Click a button and it happens
2) Upon Exiting the Workbook it happens

I tried modifying a code that I had to Reveal sheets but I can get it to operate the other way:

Code:
 
Sub HideMINUTESandMASTER()
'
Dim Sh As Worksheet
    If Sheets("Meeting Minutes").Visible = True Then
    End If
    
    If Sheets("Index").Visible = True Then
    End If
    
For Each Sh In Worksheets
    Sh.Visible = xlVeryHidden
    
    Next Sh
    
    
    Sheets("Meeting Minutes").Select
    Range("C1").Select
    
End Sub
Any suggestions???

THANKS,
Mark

Anyone chip in and tell me why this won't work?:

Code:
 
Private Sub VeryHideSheet()
Dim cVisible As Long
    Dim i As Long
    With ActiveWorkbook
        For i = 1 To Worksheets.Count
            If .Worksheets(i).Visible = xlSheetVisible Then
                cVisible = cVisible + 1
            End If
        Next i
 
    End With
 
    With ActiveWindow
        If cVisible > .SelectedSheets.Count Then
           .SelectedSheets.Visible = xlVeryHidden
        Else
            MsgBox "You cannot hide this sheet, as it" & vbNewLine & _
            "will not leave a visible sheet, and" & vbNewLine & _
            "that is not permissible with Excel", vbInformation, _
            "Sheet Management"
        End If
    End With
End Sub
But this will:

Code:
 
Private Sub VeryHideSheet()
Dim cVisible As Long
    Dim i As Long
    With ActiveWorkbook
        For i = 1 To Worksheets.Count
            If .Worksheets(i).Visible = xlSheetVisible Then
                cVisible = cVisible + 1
            End If
        Next i
 
    End With
 
    With ActiveWindow
        If cVisible > .SelectedSheets.Count Then
            .SelectedSheets.Visible = False
        Else
            MsgBox "You cannot hide this sheet, as it" & vbNewLine & _
            "will not leave a visible sheet, and" & vbNewLine & _
            "that is not permissible with Excel", vbInformation, _
            "Sheet Management"
        End If
    End With
End Sub
According to this article:
http://support.microsoft.com/kb/213609
.visible can be set to true, false or xlVeryHidden but VBA won't allow me to execute the code....I want to very hide the selected sheets.

Thanks in advance for any assistance

hi I want to use xlveryhidden in all but one worksheet in my workbook.

Ryan

Hi members,
Please let me know if anybody knew the opposite of xlVeryHidden.
I have made a sheet hidden using the below code.

Sheets("sheet1").Visible = xlVeryHidden

Now if want to make it visible, what is the macro to write.

Please advice..

Thanks

Roshin

I would like to "Archive" any worksheet that has data beyond row 14 when a user tries to delete the sheet.

I know in the ThisWorkbook code, I can edit what happens when a worksheet is added, by editing the sub "Workbook_NewSheet" and I was wondering if there is a way to do something similar for when sheets are deleted.

I know, usually, when a sheet is deleted, a confirmation message is shown, asking if the user is sure they would like to delete the sheet. I would like to avoid showing that message, and show my own, asking if the user would like to "Archive" the sheet enstead. If they select yes, I would like set the sheet as xlveryhidden, and rename it to nameArchived. If no is selected, then delete message may be shown.

Any suggestions?

I am new to VBA and thought I could accomplish this feat through various forum threads. So far so good but I'm stuck now. As the title suggest, my objective is to build these security features into a very large workbook/database:When workbook is opened all worksheets are automatically protected and hidden (xlVeryHidden);then a UserForm with a Combobox, Inputbox and CommandButtons (Ok+Cancel) is displayed;Users then select from the Combobox list (8 items) and enter a password (text string) into the Inputbox and click Ok (each listed item has its own password recorded in a hidden worksheet range);If combination of listed item and password is validated a the relevant worksheets are made visible (non relevant worksheets remain "veryHidden") and;the UserForm is either hidden/minimised(ribbon controls/right click menu etc), however should be able to call the UserForm from the current view with ease.If combination is invalid, an error message is displayed and requested to try again. Cancel button should close workbook, and top-right 'X' (exit button) should close workbook as well.I have managed, through various threads, to code steps 1 and 2, and others for the Cancel and 'X' buttons. I have also found a few threads to help with coding the inputbox for passwords but have not progressed until the combobox issue is resolved.

Where I have been struggling the most is how to assign the names in the combobox list to their respective worksheets so that when selected only those worksheets are visible.
see the relevant code below.
Can someone please, please, help.

Code for ThisWorkbook object:

	VB:
	
 Workbook_Open() 
    Dim sht As Worksheet 
     '
    Application.EnableEvents = True 
    Application.ScreenUpdating = False 
    ActiveWorkbook.Unprotect wbPassword 
    For Each sht In ActiveWorkbook.Worksheets 
        With sht 
            .Protect Password:=wsPassword, UserInterfaceOnly:=True 
            .EnableOutlining = True 
            .Visible = xlSheetVeryHidden 
        End With 
    Next sht 
    ActiveWorkbook.Protect wbPassword 
     '
    frmCtrAccess.Show 
     '
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
'
Code for Userform object:

	VB:
	
Private Sub UserForm_Initialize() 
    With Me.ComboBox1 
        .Clear 
        .AddItem "Fish" 
        .AddItem "Amphibian" 
        .AddItem "Bird" 
        .AddItem "Insect" 
        .AddItem "Mammal" 
        .AddItem "Reptile" 
        .AddItem "Animals" 
        .AddItem "Master" 
    End With 
End Sub 
 '
 'calls the ChangeCombo procedure when item is selected from combobox list
Private Sub ComboBox1_Change() 
    ChangeCombo 
End Sub 
 '
 'closes the workbook when the top-right 'X' on UserForm is clicked
Private Sub UserForm_Terminate() 
    ThisWorkbook.Close (savechanges = False) 
End Sub 
 '
 'closes the workbook when the commandbutton 'Cancel' is clicked
Private Sub CommandButtonCancel_Click() 
    ThisWorkbook.Close (savechanges = False) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Code for Module:

	VB:
	
 ChangeCombo() 
    Dim wsFish(3) As Variant 
    Dim wsAmphibian As Variant 
    Dim wsBird As Variant 
    Dim wsInsect As Variant 
    Dim wsMammal As Variant 
    Dim wsReptile As Variant 
    Dim wsAnimals As Variant 
    Dim wsMaster As Variant 
    Dim strComboValue As String 
     '
    wsFish(3) = Sheets(Array("Freshwater Fish", "Saltwater Fish", "Sharks_Rays", "Sea Creatures")) 
     '
    wsAmphibian(1) = Sheets(Array("Frogs_Toads", "Salamanders")) 
     '
    wsBird(5) = Sheets(Array("Backyard Birds", "Ground Birds", "Birds of Prey", "Owls", "Seabirds", "Woodpeckers")) 
     '
    wsInsect(8) = Sheets(Array("Arachnids", "Beetles", "Butterflies", "Centipedes", "Bugs", "Flies", "Grasshoppers", 
"Dragonflies", "Social Insects")) 
     '
    wsMammal(5) = Sheets(Array("Carnivores", "Hoofed Mammals", "Marine Mammals", "Primates", "Rabbits", "Rodents")) 
     '
    wsReptile(3) = Sheets(Array("Crocodilians", "Lizards", "Snakes", "Turtles")) 
     '
    wsAnimals(30) = Sheets(Array("Freshwater Fish", "Saltwater Fish", "Sharks_Rays", "Sea Creatures", "Frogs_Toads",
"Salamanders", "Backyard Birds", "Ground Birds", _ 
    "Birds of Prey", "Owls", "Seabirds", "Woodpeckers", "Grasshoppers", "Dragonflies", "Social Insects", "Carnivores",
"Hoofed Mammals", "Marine Mammals", _ 
    "Primates", "Rabbits", "Rodents", "Crocodilians", "Lizards", "Snakes", "Turtles")) 
     '
    wsMaster(31) = Sheets(Array("Settings", "Freshwater Fish", "Saltwater Fish", "Sharks_Rays", "Sea Creatures",
"Frogs_Toads", "Salamanders", "Backyard Birds", _ 
    "Ground Birds", "Birds of Prey", "Owls", "Seabirds", "Woodpeckers", "Arachnids", "Beetles", "Butterflies", "Centipedes",
"Bugs", "Flies", "Grasshoppers", _ 
    "Dragonflies", "Social Insects", "Carnivores", "Hoofed Mammals", "Marine Mammals", "Primates", "Rabbits", "Rodents",
"Crocodilians", "Lizards", "Snakes", _ 
    "Turtles")) 
     '
    Let strComboValue = frmCtrAccess.ComboBox1.Value 
     '
    Select Case strComboValue 
    Case Is = "Fish" 
        wsFish.Visible = xlSheetVisible 
        wsFish.Select 
         '
    Case Is = "Amphibian" 
        wsAmphibian.Visible = xlSheetVisible 
        wsAmphibian.Select 
         '
    Case Is = "Bird" 
        wsBird.Visible = xlSheetVisible 
        wsBird.Select 
         '
    Case Is = "Insect" 
        wsInsect.Visible = xlSheetVisible 
        wsInsect.Select 
         '
    Case Is = "Mammal" 
        wsMammal.Visible = xlSheetVisible 
        wsMammal.Select 
         '
    Case Is = "Reptile" 
        wsReptile.Visible = xlSheetVisible 
        wsReptile.Select 
         '
    Case Is = "Animals" 
        wsAnimals.Visible = xlSheetVisible 
        wsAnimals.Select 
         '
    Case Is = "Master" 
        wsMaster.Visible = xlSheetVisible 
        wsMaster.Select 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,
Hope you can help me with this - I'm going mad trying to figure it out. Using Excel2010 in Windows7.
I have a UserForm
1: TextBox for Input Username (txtUsernameIn)
2: TextBox for Input Password (txtPasswordIn)
3) Submit Button (cmdSubmit1)

I have a Worksheet
ColA: Username
ColB: Password
And this entire Range A:B, I have named "UserRegister"

I have code, attached to the cmdSubmit1 button on my UserForm. The purpose is to allow access to a veryhidden Worksheet "ADMIN".
The code 'works' if I enter in both the correct UserName and Password (ie: the code completes & Admin sheet opens up)
The code 'works' if I enter in the correct UserName and the wrong password (ie: code completes it's run, my msg pops up to say there is wrong input, unloads the form & sheet ADMIN stays veryhidden)

However, if I enter the wrong username (with either right or wrong password) the code breaks and I get a debug error. I know my code is obviously wrong/missing something etc - but I can't work it out! The line of code highlighted on the break, is between the *** below


	VB:
	
 cmdSubmit1_Click() 
    Dim Username As String 
    Username = txtUserNameIn.Text 
    Dim password As String 
    password = txtPasswordIn.Text 
     'Check to see if data is entered into field: txtUserNameIn
    If IsNull(Me.txtUserNameIn) Or Me.txtUserNameIn = "" Then 
        MsgBox "You must enter your username.", vbOKOnly, "Required Data" 
        Me.txtUserNameIn.SetFocus 
        Exit Sub 
    End If 
     
     'Check to see if data is entered into field: txtPasswordIn
    If IsNull(Me.txtPasswordIn) Or Me.txtPasswordIn = "" Then 
        MsgBox "You must enter your Password (case sensitive).", vbOKOnly, "Required Data" 
        Me.txtPasswordIn.SetFocus 
        Exit Sub 
    End If 
     
     'Check to see if the Username & Password entered is a valid username in the 'User Register'
     '****************
    If Username = WorksheetFunction.VLookup(Me.txtUserNameIn.Value, Range("UserRegister"), 1, 0) Then 
         '****************
        If password = WorksheetFunction.VLookup(Me.txtUserNameIn.Value, Range("UserRegister"), 2, 0) Then 
            Sheets("ADMIN").Visible = xlSheetVisible 
            MsgBox "Password & Username Accepted" 
            Unload Me 
            Sheets("ADMIN").Select 
            Range("A1").Select 
        Else 
            Sheets("ADMIN").Visible = xlVeryHidden 
            MsgBox "Username & Password Combination Not Accepted, Workbook will close" 
            Unload Me 
             'ThisWorkbook.Close  (I'm not wanting to go-live with this bit yet, so commented out)
        End If 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Greatly appreaciate your help!
Thanks
Annie

hi,

i have a dynamic named range which provides the sheets names in a list (let's say A2 = Austria, A3 = Germany, A4 = Poland) This list can vary in size depending on the user so I have an offset in the named range to capture all the cells in column A with a name in it.

I would like to use an IF statement to veryhide certain sheets based on this named range.

something like:

Public Sub test()
Sheets(Array(Range("MyRange"))).Visible = xlVeryHidden
End Sub

with the above i get the following error:
Run-time error '13':
Type mismatch

any suggestions please.

Thank you
Paul

I have exhausted all possibilities for finding the problem in this code that gives me the above error


	VB:
	
 Auto_Open() 
     '
     ' Auto_Open Macro
     ' Macro recorded 9/7/2005 by MKNUDS1
     '
     
     '
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Worksheets("Sheet1").Unprotect 
    Worksheets("Sheet2").Unprotect 
    Worksheets("Sheet3").Unprotect 
    Sheets("Sheet2").Visible = True 
    Sheets("Sheet3").Visible = True 
    Sheets("Sheet3").Select 
    Range("D12").Value = "=NOW()" 
    Range("D13").Value = "=TODAY()" 
    Range("D15").Value = "=D13-D14" 
    Selection.NumberFormat = "0" 
    Range("D12").Select 
    Selection.Copy 
    Range("E1").Select 
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ 
    xlNone, SkipBlanks:=False, Transpose:=False 
    Range("E1").Select 
    Selection.TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _ 
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ 
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _ 
    :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _ 
    TrailingMinusNumbers:=True 
    Range("F2").Select 
    Selection.TextToColumns Destination:=Range("F3"), DataType:=xlDelimited, _ 
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ 
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ 
    :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _ 
    TrailingMinusNumbers:=True 
    Range("D13").Select 
    Selection.Copy 
    Range("E5").Select 
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ 
    xlNone, SkipBlanks:=False, Transpose:=False 
    Application.CutCopyMode = False 
    Selection.TextToColumns Destination:=Range("E6"), DataType:=xlDelimited, _ 
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ 
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ 
    :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _ 
    TrailingMinusNumbers:=True 
    Dim SetYear 
    SetYear = Range("G6").Value 
    Range("D14").Value = "01/01/" & SetYear 
    Range("D15").Select 
    Selection.Copy 
    Range("E7").Select 
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ 
    xlNone, SkipBlanks:=False, Transpose:=False 
    Sheets("Sheet3").Select 
    Dim RollSetX 
    RollSetX = Range("G6").Value - 2000 
    Dim JulianDateX 
    JulianDateX = Range("E7").Value 
    If Range("F3").Value > 7 Or Range("G2").Value = "PM" Then 
        Range("E5").Select 
        Selection.Copy 
        Sheets("Sheet1").Select 
        Range("B3").Select 
        ActiveSheet.Paste 
        Sheets("Sheet1").Select 
        [B]Range("B1").Value = "13" & RollSetX & JulianDateX & "0001"[/B] 
        Range("B2").Value = "14" & RollSetX & JulianDateX & "0001" 
    Else 
    End If 
    If Range("F3").Value < 7 Or Range("G2").Value = "AM" Then 
        Dim ProdDateY 
        ProdDateY = Range("E5").Value - 1 
        Sheets("Sheet1").Select 
        Range("B3").Value = ProdDateY 
        Range("B1").Value = "13" & RollSetX & JulianDateX - 1 & "0001" 
        Range("B2").Value = "14" & RollSetX & JulianDateX - 1 & "0001" 
    Else 
    End If 
    Sheets("Sheet1").Select 
    Range("B3").Select 
    Selection.Locked = False 
    Selection.FormulaHidden = False 
    Range("A9").Select 
    Selection.ClearContents 
    Worksheets("Sheet1").Protect 
    Worksheets("Sheet2").Protect 
    Worksheets("Sheet3").Protect 
    Sheets("Sheet2").Visible = xlVeryHidden 
    Sheets("Sheet3").Visible = xlVeryHidden 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The line in Bold is the one that returns the error...
Last time I encountered something like this was when a sheet was password protected, but here the first step unprotects everything. so i really do not know
thanks

Hello, new to forum and very much a novice. I am working on a spreadsheet and would like it to perform the following functions:

1) Open to a warning worksheet telling you to enable macros where all other worksheets are hidden unless you enable macros. When you enable macros the warning worksheet is hidden and all others appear.

I found the following code for this and it works:

(placed in "This Workbook")

	VB:
	
) 
    Dim myCount 'This line of code is optional
    Dim i 'This line of code is optional
    On Error Resume Next 
    myCount = Application.Sheets.Count 
    Sheets(1).Visible = True 
    Range("A1").Select 
    For i = 2 To myCount 
        Sheets(i).Visible = xlVeryHidden 
        If i = myCount Then 
        End If 
    Next i 
    ActiveWorkbook.Save 
End Sub 
Private Sub Workbook_Open() 
    Dim myCount 'This line of code is optional
    Dim i 'This line of code is optional
    On Error Resume Next 
    myCount = Application.Sheets.Count 
    For i = 2 To myCount 
        Sheets(i).Visible = True 
        If i = myCount Then 
            Sheets(1).Visible = xlVeryHidden 
        End If 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

2) Once macros are enabled, automatically save and close the Excel file if inactive after 5 minutes.

I found the following code for this and it works:

(placed in "This Workbook")

	VB:
	
 Workbook_Open() 
    MsgBox "This workbook will auto-close after 5 minutes of inactivity" 
    Call SetTime 
End Sub 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Call Disable 
End Sub 
 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 
    Call Disable 
    Call SetTime 
End Sub 
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target _ 
    As Excel.Range) 
    Call Disable 
    Call SetTime 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

(placed in "Module1")

	VB:
	
 
 
Sub SetTime() 
    DownTime = Now + TimeValue("00:05:00") 
    Application.OnTime DownTime, "ShutDown" 
End Sub 
 
Sub ShutDown() 
    ThisWorkbook.Save 
    ThisWorkbook.Close 
End Sub 
 
Sub Disable() 
    On Error Resume Next 
    Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", _ 
    Schedule:=False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I cannot figure out how to combine these two actions. I am working on a project for work and this would save a great deal of frustration when people leave the workbooks open. Thanks in advance for your time and expertise.

Hi!

I have a macro, that when running needs to display certain sheets that should be hidden again when the macro is done. I solve this by using the code


	VB:
	
 
 'macro code
Sheets("Sheet1").Visible = xlVeryHidden 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But what if the macro fails? Or if the user ends it by pressing Esc? Then he will be able to see the hidden sheet. Is there something I can write in the code that makes the macro hide the sheet, then show the error message?

With using a username/password code that someone supplied on here (sorry, I forgot the name of the user) I have slightly modified it to fit my specific A-Level project.

This is the code I currently have:


	VB:
	
 Cancel_Click() 
    Sheets("Protected").Visible = xlVeryHidden 
    MsgBox "Please Enter Password To Log In" 
    Unload Me 
    ThisWorkbook.Close 
End Sub 
 
Private Sub EnterPassword_Click() 
    Dim password As String 
    password = PasswordInput.Text 
     
     
    If password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then 
        Sheets("Protected").Visible = xlVeryHidden 
        Sheets("Blank").Visible = xlVeryHidden 
        MsgBox "Log In Successful" 
        Unload Me 
    Else 
        Sheets("Protected").Visible = xlVeryHidden 
        Sheets("Blank").Visible = xlVeryHidden 
        MsgBox "Password Not Accepted, Workbook will close" 
        Unload Me 
        ThisWorkbook.Close 
    End If 
     
     
     
End Sub 
 
Private Sub UpdatePassword_Click() 
    Dim password As String 
    password = PasswordInput.Text 
    If password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then 
        User = UserList.Value 
        Sheets("Blank").Range("A1").Value = User 
        Unload Me 
        UserForm2.Show 
    Else 
        MsgBox "Incorrect Password" 
    End If 
     
     
     
End Sub 
 
 
 
Private Sub UserForm_Terminate() 
    Dim password As String 
    password = PasswordInput.Text 
     
    If PasswordInput.Text = "" Or UserList.Value = "" Then 
         
        MsgBox "must use this form, workbook will close" 
        ThisWorkbook.Close 
    ElseIf password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then 
         
         
         
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I would like to know, is:

- How do I change it so that when the password is entered correctly, it goes to a specific sheet that I have already made?
- And, where do I copy and paste the code into my own spreadsheet? Where do i imput the code?

If it is any help, I am using Office 07.

Thank you

Hi,

I am trying to write a piece of code which will make all sheets in my workbook become VeryHidden (except the last).

The code works, but will not hide any Charts. The charts just stay unhidden

Here is my code:


	VB:
	
 
Dim sh As Worksheet 
 
For Each sh In Worksheets 
    sh.Visible = xlVeryHidden 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there a way to hide all sheets including the charts?

Thanks!

Is there a way, other than typing in the code longhand (e.g. thisworkbook.sheets("name").visible=xlveryhidden) for each worksheet in the workbook, to hide every worksheet except one when the workbook is opened?

I'm familar UserInterfaceOnly:= True

I'm on excel 2002 on XP and I can't get it to work. e.g when I run a macro to copy some data I get an error saying it's protected. I've had a hunt around on Microsoft but I'm still at a loss.


	VB:
	
 Workbook_Open() 
     
    Call CreateMenu 
     
    Dim wSht As Worksheet 
    Dim pWord As String 
     
    pWord = "****" 
     
    For Each wSht In ActiveWorkbook.Sheets 
        wSht.Protect _ 
        Password:=pWord, _ 
        DrawingObjects:=True, _ 
        Contents:=True, _ 
        Scenarios:=True, _ 
        UserInterfaceOnly:=True 
    Next wSht 
     
    Worksheets("MenuSheet").Visible = xlVeryHidden 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas?

I have a 'very hidden' sheet and am having trouble writing to it. For example, i have 'sheet1' veryhidden and want to make the value in a1 = 1. Is there a way to use this sheet while it is still hidden? I want to be able to work in this sheet while it is hidden.

Can you password protect a xlveryhidden sheet like you can just a hidden sheet? I haven't been able to find how to do this.

If my cells are hidden and locked with a password, are they still editable with vba or do I have to unlock them and relock them after the code? This is my example. I have a series of formulas on one sheet (Sheet1) that reference values on several other sheets (sheets 2 and 3). If I have the cells hidden and locked on sheet1, will they update themselves based on changes to the sheets 2 and 3? Or do I need to unlock the cells on sheet1 to allow them to update and relock them after the vba is run?

Are efforts to password protect my project worth while? I've heard it is not terribly difficult to hack a password for excel or vba. Is this still the case even if the password is long?

Sorry for being so long. Thanks for the help. You guys are awesome!

Dave...

I have a workbook containing several sheets. When opening the workbook I want XL to display Sheet3.

I'd also like to hide some other sheets in the workbook and on sheet3 display command-buttons connected to VBA to hide/unhide the named workbooks.

I've found a VBA code to hide/unhide one selected sheet name but in what way should I change the code to specify several sheets? (trying to learn VBA...).
I'd like to hide sheet1, sheet2, sheet5.

Sub HideSheet2()
Sheets("Sheet2").Visible = xlVeryHidden
End Sub

Sub ViewSheet2()
pword = Application.InputBox("Enter password to view sheet2", "PASSWORD REQUIRED")

If pword = "PASSWORD" Then

Sheets("Sheet2").Visible = True
End If
End Sub

Any ideas?
/Daniel

Hi Guys

I see this is a problem posted before but unfortunately the solutions have not worked for me.

The following code gives an error:


	VB:
	
 
Private Sub cmdOK_Click() 
    Dim a, u, p, w(), i As Long, db As Worksheet, Flg   As Boolean 
    Dim j   As Long, x, y, c As Long, rSource As String 
    Set db = Sheets("DashBoard"): Flg = False: c = 0: x = 0 
    With db 
        a = .Range("a1").CurrentRegion 
    End With 
    u =[COLOR=red] UCase[/COLOR](Me.tbUN): p = Me.tbPW: Flg = False 
    With Application 
        x = .Match(u, .Index(a, 0, 1), 0) 
    End With 
    If Not IsError(x) Then 
        If Application.Index(a, x, 2) = p Then Flg = True 
        If Flg Then 
            Redim w(1 To UBound(a, 2) - 2) 
            For j = 3 To UBound(a, 2) 
                If UCase(a(x, j)) = "A" Then c = c + 1: w(c) = a(1, j) 
            Next 
        Else 
            MsgBox "Incorrect Password", vbCritical + vbOKOnly 
            Exit Sub 
        End If 
    Else 
        MsgBox "Incorrect User Name", vbCritical + vbOKOnly 
        Exit Sub 
    End If 
    For i = 1 To Sheets.Count 
        If Sheets(i).Name  "Introduction" Then 
            If IsError(Application.Match(Sheets(i).Name, w, 0)) Then 
                On Error Resume Next 
                Sheets(i).Visible = xlVeryHidden 
            Else 
                Sheets(i).Visible = xlSheetVisible 
            End If 
        End If 
    Next 
    On Error Goto 0 
    With db.Range("aa1") 
        .Resize(100).Clear 
        .Value = "Sheet Names" 
        .Offset(1).Resize(c) = Application.Transpose(w) 
    End With 
    Unload Me 
    UserForm2.Show 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, I get no problems calling this from my personal computer. I have used this exact code on projects before with no problems. I have tried on two different colleagues' PC's before with the same results??

Thanks for all your time on this.

Sheets("Data").Visible=xlVeryHidden

Above is the code that can be used to hide "Data" sheet.

Can I know what are the additional code that I needed to add in order to hide the "Data" and "Data1".

After I hide "Data" and "Data1", can I know how do make it visible again (unhide) ?

Hey all,

I'm having a tricky time trying to figure out how to remove this error. The script stops on the following line:


	VB:
	
 sMaster = Sheets("MasterSheet") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The worksheet MasterSheet exists, however it is set to xlVeryHidden.

Any ideas what the problem could be? I've pasted the remaining code below:

Thanks in advance!


	VB:
	
 UserForm_Initialize() 
     'On Error Resume Next
     
    Dim sMaster As Worksheet 
    Dim i, iCol, iRow, intRowSource As Integer 
    Dim strRowSource As String 
    Dim pendingRecords As String 
    Dim cCont As Control 
     
    Private Sub UserForm_Initialize() 
         'On Error Resume Next
         
        Dim sMaster As Worksheet 
        Dim i, iCol, iRow, intRowSource As Integer 
        Dim strRowSource As String 
        Dim pendingRecords As String 
        Dim cCont As Control 
         
        Set sMaster = Sheets("MasterSheet") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

Question:-
How do i create a shortcut key for buttons placed on a worksheet?

Details:-
Implemeiting on previous advice from Dave

The approach I normally take is to have all but one sheet XlVeryHidden and leave them that way always. The sheet that is visible is simply a blank sheet without gridlines scroll bars etc. in thread
http://www.ozgrid.com/forum/viewthread.php?tid=843

I have successfully started the work.

Now the problem is, i have placed some buttons on the worksheet, say "First", "Last" which bring the respective records.

Help Required for:
How to create a shortcut key for these buttons?
(In VB i remember having some option while entring captions, to do this)

Any such facility when a button is placed on worksheet???

Hey there,

I've got a question I can't solve by myself. It's about pasting columns. The screenshots will show the following problem.

Here is the sheet where I entry new data.
From left to right: Productnumber/article/supplier/price per item/starting nr of articles/order threshold/date 1st order/date of delivery

When I press the button "verwerk" the data will be transported to the sheet "Voorraad verloop" shown underneath

As you can see the first problem consists of the placement of the first table. I can't get it to the first column. The second problem is that when I enter a new product like in the first image, it overwrites the current data in the sheet "voorraadverloop". See the image below.

I used the following macro:

	VB:
	
 CommandButtonVerwerknieuw_Click() 
    Dim iLC As Integer 
    Dim FindString As String 
    Dim rng As Range 
    Dim Lr As Long 
    If Application.WorksheetFunction.CountA(Range("A4:F4")) < 6 Then 
        MsgBox ("Niet volledig ingevuld"): Exit Sub 
    End If 
    FindString = Range("A4") 
    If Trim(FindString)  "" Then 
        With Sheets("Voorraadscherm").Range("A29:A65536") 
            Set rng = .Find(What:=FindString, _ 
            After:=.Cells(.Cells.Count), _ 
            LookIn:=xlValues, _ 
            lookat:=xlWhole, _ 
            SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, _ 
            MatchCase:=False) 
            If Not rng Is Nothing Then 
                MsgBox ("Productnummer bestaat al"): Exit Sub 
                 
            Else 
                Lr = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1 
                With Sheets("Data") 
                    For iKol = 1 To 7 
                        Sheets("Data").Cells(Lr, iKol) = Sheets("Nieuw product invoeren").Cells(4, iKol) 
                    Next 
                    If Worksheets("Voorraadscherm").Range("A4") = "" Then 
                        Lr = 4 
                    Else 
                        Lr = Worksheets("Voorraadscherm").Range("A3").End(xlDown).Row + 1 
                    End If 
                    With Sheets("Voorraadscherm") 
                        .Cells(Lr, 1) = Sheets("Nieuw product invoeren").Cells(4, 1) 
                        .Cells(Lr, 2) = Sheets("Nieuw product invoeren").Cells(4, 2) 
                        .Cells(Lr, 3) = Sheets("Nieuw product invoeren").Cells(4, 5) 
                        .Cells(Lr, 4) = Sheets("Nieuw product invoeren").Cells(4, 3) 
                        .Cells(Lr, 5) = Sheets("Nieuw product invoeren").Cells(4, 7) 
                        .Cells(Lr, 6) = Sheets("Nieuw product invoeren").Cells(4, 8) 
                        .Cells(Lr, 7) = Sheets("Nieuw product invoeren").Cells(4, 4) 
                        .Cells(Lr, 10) = Sheets("Nieuw product invoeren").Cells(4, 9) 
                    End With 
                    NieuwVoorraadverloop 
                    Sheets("Nieuw product invoeren").Range("A4:F4").ClearContents 
                    Sheets("Nieuw product invoeren").Range("H4").ClearContents 
                    Sheets("Nieuw product invoeren").Visible = xlVeryHidden 
                    Sheets("Inkoopscherm").Select 
                End With 
            End If 
        End With 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
 NieuwVoorraadverloop() 
    Dim iLC As Integer 
    Dim Datum As String 
    Sheets("Template").Visible = True 
    Sheets("Template").Columns("A:G").Copy 
    iLC = Cells.Find(What:="*", SearchOrder:=xlByColumns, _ 
    SearchDirection:=xlPrevious).Column 
    Sheets("Voorraadverloop").Select 
    Cells(1, iLC + 2).EntireColumn.Select 
    ActiveSheet.Paste 
    Sheets("Template").Visible = xlVeryHidden 
    Sheets("Inkoopscherm").Select 
    Sheets("Nieuw product invoeren").Select 
    Range("A4").Select 
    Selection.Copy 
    Sheets("Voorraadverloop").Activate 
    ActiveCell.Offset(5, 0).Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    Range("A1").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want the macro to create a new table next to the previous table in the sheet "voorraadverloop". In order to do this, it copies a template I created (this is placed on the sheet "template).

If things aren't clear enough, feel free to ask for an explenation.

Regards,

Barbaar

I have 7 Sheets in this particular workbook:
Front PageDrillingAnchorageGroutingSoil NailDriven PilesMini Piles
On sheet "Front Page" I have a drop down box in cell C6 (6,3). There are six options in the drop down box with the same names as the sheets from Drilling to Mini Piles.

When Drilling is picked from the List I would like all the other sheets to be hidden except from "Front Page" and "Drilling".

The code I have tried to use is:

	VB:
	
 Workbook_Selection() 
    If Cells(6, 3) = "Drilling" Then 
        Worksheets("Drilling").Visible = xlSheetVisible 
        Worksheets("Anchorage").Visible = xlVeryHidden 
        Worksheets("Grouting").Visible = xlVeryHidden 
        Worksheets("Soil Nail").Visible = xlVeryHidden 
        Worksheets("Driven Piles").Visible = xlVeryHidden 
        Worksheets("Mini Piles").Visible = xlVeryHidden 
         
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have written this code in the workbook but does not work.

Cheers


No luck finding an answer? You could always try Google.