Excel VBA Static (Non-Volatile) Variable - Userform

Question

Hello again Stack Overflow -

Working with a userform in Excel 2010 VBA, I have a textbox called "reviewerName" which should to be given a default value set by the user. Right now I have the default as:

Private Sub userform_initialize()
    reviewerName.text = "Your name here"
End Sub

When I open the form, the already has "Your name here" filled out on the textbox. Great! But if someone else overwrites it with their name, how do I set the default to the new name? If I close and open the form, can the reviewerName = "Bob"? What is the command to update the reviewerName variable so that it is static and can be used next time the workbook is opened?

Thanks!

reviewName isn't a variable. It's a form field. Perhaps you just want to stick this exact same code into the userform_activate() event instead of userform_initialize()? — JNevill
There isn't a command to persist variables like that. For forms like this, I'll usually use something like Application.UserName or Environ$("USERNAME"). — Comintern
Is it possible to create a persistent variable by maybe on a command button, writing the new username to cell(1,1) on a sheet and then on the open look at cell(1,1) on a sheet? — Arthur D. Howland

Arthur D. Howland Arthur D. Howland · Accepted Answer · 2016-10-28T21:00:29

Working at it, one way to do it is to have the textbox refer to a value already on the spreadsheet, effectively giving a "static" like variable.

Private Sub userform_initialize()
   reviewerName.text = Cell(1,24) 'form will look for a value in column X Row 1.
End Sub

Then when there's an action to submit the form, one of the commands should be:

Private Sub submit_click()
...
Cells(1,24) = reviewerName.text 'writes a value in column X row 1.
...
End Sub

When you open the form again it will draw the value from that cell.

Excel VBA Static (Non-Volatile) Variable - Userform

1 Answers