0

I'm trying to create a variable that is stored after a user's input when opening the workbook, and that should be available for all modules and subs afterward without any further input or note.

I have tried this so far, but I always get a message error:

Private Sub Workbook_Open()
Public UserID As String
UserID = InputBox("Please insert your username")
End Sub

After, that UserID should be automatically replaced by the inserted value in a different Sub and Module at the following step:

Path = "C:\Users\" & UserID & "....xlsm"

Instead, I get an error message that the Path could not be found due to the missing UserID value.

Any Idea how to resolve this?

  • 1
    Does this answer your question? [UserProfile environ on vba](https://stackoverflow.com/questions/42091960/userprofile-environ-on-vba) – Pᴇʜ Jan 21 '20 at 10:40
  • To your specific question, the declaration should be in a normal module, outside any routines. – Rory Jan 21 '20 at 10:47
  • that means that there is no way of defining variables at the beginning, before any action? –  Jan 21 '20 at 11:15
  • There is, see [here, #5](https://www.excel-easy.com/vba/examples/variable-scope.html). You declare it in the module, but assign on `Workbook_Open` event, even though module will contain only this declaration and no subs/functions – Vitaliy Prushak Jan 21 '20 at 11:32
  • Here is the [picture](https://ibb.co/2nRRssJ) of how it should look like. – Vitaliy Prushak Jan 21 '20 at 11:41
  • thanks! i understand the logic. I just get a compile error about an expected variable or procedure, not module :( –  Jan 21 '20 at 12:09
  • @user12408889 Did you see the link in my first comment? You don't need the user to enter its userID you can use `Environ` to get it from the system so the user doesn't need to enter it. – Pᴇʜ Jan 21 '20 at 13:38
  • now I got it! thanks a lot, very useful :) –  Jan 22 '20 at 14:19

0 Answers0