Excel/Excel 2003

Advertisement


Question
QUESTION: Hi
I have a spreadsheet which I will get some youngster to enter data into it.

I need a code that when they open the spreadsheet it;

1) prompts them for the user name and password.
2) Once they enter their user name it should then save it in another sheet tab called log with their user id and time.



Regards
MK

ANSWER: In the Visual Basic Editor, in the Workbook object, enter

Private Sub Workbook_Open()
   Dim userID As String
   userID = InputBox("Enter User Name:", , "User Name")
   With ThisWorkbook.Sheets(1)
       .Range("a1") = userID
       .Range("a2").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
       .Range("a2").Value = Now
       .Range("a2").EntireColumn.AutoFit
   End With
End Sub

---------- FOLLOW-UP ----------

QUESTION: This is great sir. Thank you.
Just one thing. When a 2nd user logs in it overwrites the first user. I need to keep a log of all users accessing the spreadsheet with the date stamp.
There will only be a single user at a time accessing the sheet.

Regards
Mustafa

ANSWER: Private Sub Workbook_Open()
  Dim userID As String
  Dim enterHere as range
  Set enterHere = thisWorkbook.sheets(1).range("a1")
  userID = InputBox("Enter User Name:", , "User Name")
  do until enterHere = ""
     Set enterHere = enterHere.offset(1)
  loop
  enterHere = userID
  enterHere.offset(0,1).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
  enterHere.offset(0,1).Value = Now
  enterHere.range("a1:b1").EntireColumn.AutoFit
End Sub

---------- FOLLOW-UP ----------

QUESTION: Thank you very much for this sir.

This is great.

Just last thing, is it possible to hide all sheets until user put his username? and once he enters his name sheets become visible.

Once again thank you. I have given my ratings.

Regards
Mustafa

Answer
You can't hide all sheets, because Excel requires that at least one sheet of a workbook is visible. You can, however, hide all sheets except one.

Sub Macro2()
  Dim sht As Worksheet
'to make all sheets visible
  For Each sht In ThisWorkbook.Worksheets
    If Not sht.Visible Then
       sht.Visible = True
    End If
  Next sht
'to hide all sheets except first one
  For Each sht In ThisWorkbook.Worksheets
    If sht.Visible Then
       If sht.Name <> ThisWorkbook.Sheets(1).Name Then
         sht.Visible = False
       End If
     End If
   Next sht
End Sub

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Stuart Resnick

Expertise

I can answer questions relating to MS Excel formulas, or to programming with vba (Visual Basic for Applications) in the Excel environment. Please follow the following guidelines: your question should focus on one specific issue you want to learn. It's beyond the scope of this free service for me to create entire projects or complex vba solutions for you from scratch. You should be able to do most of the work yourself, and come here when you need help with a specific point you're stuck on. ALWAYS include a simple, concrete example illustrating what you want to learn. Explain this example in detail in the text of your question (what data is in which cells of which sheets, etc). Be very precise about the results you want, using this sample to make the logic clear. Always keep these examples SIMPLE. Never e.g. use 18 worksheets in your example if using 2 or 3 will do. Never use ranges like AI567:BB865 if using a range like A1:B3 will do. Thanks.

Experience

As a consultant, I've designed Excel tools since the 90s, working for the Federal Reserve Bank, AT&T, and (currently) Gap Inc.

Education/Credentials
My only "education" comes from 2 decades of doing spreadsheet/programming work, with major SF Bay Area corporations such as AT&T, Federal Reserve Bank, and Gap Inc.

©2016 About.com. All rights reserved.