Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am currently using the Workbook_Open event to show Userform1 which captures the application.username, which uses a button click to generate the username into a cell.

    I want to lose the userform, but have the username captured in a cell from the wb_open event in vbProperCase. Is this possible?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Like this?

    Thisworkbook.Worksheets("Sheet1").Range("A1").Valu e=Application.username
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use

    Code:
    Private Sub Workbook_Open()
      Worksheets("MySheet").Range("A2") = StrConv(Application.UserName, vbProperCase)
    End Sub
    Modify the name of the sheet and the cell address as needed.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Note that for many users, the Office username is not properly set during first load and is something silly like "CompanyUser" or "Admin".
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Private Sub Workbook_Open()
    ThisWorkbook.Worksheets("Input Sheet").Range("H2").Value = Application.UserName
    End Sub

    Private Sub Workbook_Open()
    Worksheets("Input Sheet").Range("H2") = StrConv(Application.UserName, vbProperCase)
    End Sub


    Thanks, but these are not firing?? I have a space in my sheet name, does this matter?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You *must* place the code in the ThisWorkbook module, and you must of course enable macros when you open the workbook.

    It doesn't matter whether there's a space in the sheet name, as long as the exists in that workbook.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, and apologies, this does work, I know what the problem is, but I will start another thread for that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •