About

Hi,

This is a personal blog created as a way of giving back in a small way after years of enjoying free stuff online. It is also a place to practice and improve my writing skills. My interests include cycling, electronics, Microsoft Excel, computing, photography, codes, alternative energy and geocaching.

Why is the blog called the Piffle Lab? Because that’s what I expect will end up writing. Don’t get me wrong, I’m not deliberately making anything up that goes here, it is just what I expect will come out. The other reason for choosing the name is that I couldn’t think of anything else that had not already been taken.

Get some piffle now!

 

Advertisements
  1. I could REALLY use some further help with your hiding of sheets based on user name VBA code. I got it to work, except for when I added multiple users, then it stopped working… 😦

  2. Rasmus Juul Christensen

    Hi Gary

    I found “Hiding sheets using VBA and a password”
    https://pifflelab.com/2012/06/30/excel-using-vba-to-hide-sheets-from-other-users/

    Very usefull!
    But is it possible to change the code, so that it closes/open all sheets in the woorkbook except for one sheet?
    My sheets have various names except for my main sheet.

    Best regards
    Rasmus

    • Hi Rasmus,

      One way that I can think of to do this is to modify the code so that it loops through all but the first sheet and changes the visibility. Worksheets.Count returns the number of sheets in the workbook, so by looping from 2 to the number of sheets, it should do what you want. Try the code below. Note that it will probably give an error if you only have one sheet – although I’m not sure as I haven’t properly tested it. The sheet you want to keep visible needs to be the first sheet, at least using this code.

      Sub HideSheets()
      ‘ This macro toggles worksheets listed between visible and very hidden and requires a password to run

      Dim myPassword As String

      myPassword = “password” ‘ Set password here
      Password = InputBox(“Enter Password”)

      If Password = “” Then Exit Sub ‘ Exit if null input or cancel

      ‘ Incorrect password
      If Password myPassword Then
      MsgBox Title:=”Warning”, prompt:=”Incorrect Password”
      Exit Sub
      End If

      On Error Resume Next

      ‘ Correct password
      If Worksheets(2).Visible = True Then ‘ Check if the second sheet is visable. If it is it is assumed all are.
      ‘ Hide all but first sheet
      For numSheets = 2 To Worksheets.Count
      Worksheets(numSheets).Visible = xlSheetVeryHidden
      Next numSheets
      Else
      ‘ Unhide all sheets
      For numSheets = 1 To Worksheets.Count
      Worksheets(numSheets).Visible = True
      Next numSheets
      End If

      End Sub

      Private Sub Auto_Close()
      ‘ This macro hides sheets when the user closes the workbook
      ‘ It is a “Private Sub” to prevent it being listed with outher macros

      On Error Resume Next

      ‘ When workbook is closed hide all but first sheets
      For numSheets = 2 To Worksheets.Count
      Worksheets(numSheets).Visible = xlSheetVeryHidden
      Next numSheets

      End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: