Excel Add-in with some info stored

Question

I have created a UserForm1 in Excel and saved it as an add-in. This add-in works fine but it does not store some data that I need (does not store it in itself not in the opened excel). I have to store some information in cells A1 and A2 (in A1 Username, in A2 today's date).

When I run this add-in the UserForm1 does not contain these values.

Is there a way how I can store the UserName and get the updated date?

Here is the code for UserForm1:

Private Sub UserForm1_Initialize()    
Me.DocumentName.Text = ActiveWorkbook.FullName
DocumentName.Visible = False

TextBoxDate.Value = Worksheets("Sheet1").Cells(2, "A").Value
TextBoxDate.Value = CDate(TextBoxDate.Value)

UserName.Visible = False
Userform1.UserName.Text = CStr(Range("A1").Value)

'If A1 is empty pops up a UserRegister form
If UserName = "" Then 
UserRegister.Show
End If    
End Sub

UserRegister form code:

Private Sub UserName_Change()
Sheets("Sheet1").Range("A1") = UserName.Text
End Sub

' I want to store the UserName, so the user does not have to enter it every single time
Private Sub CommandButtonGO_Click()
ThisWorkbook.Save 
Unload Me
End Sub

To get the date I just use the formula =TODAY() in Cell A2. I know there are other ways, but I found this one very simple.

Use `ThisWorkbook.Worksheets("Sheet1").Cells(2, "A").Value`, `ThisWorkbook.Worksheets("Sheet1").Range("A1").Value` and `ThisWorkbook.Sheets("Sheet1").Range("A1").Value`. If you are to store the Date of Username change, you should remove the formula and set the value within `Private Sub UserName_Change()` — PatricK, Sep 02 '15 at 02:15

score 0 · Accepted Answer · answered Sep 02 '15 at 04:20

Can you try this?

UserForm UserForm1:

Private Sub UserForm1_Initialize()
    Me.DocumentName.Text = ActiveWorkbook.FullName
    DocumentName.Visible = False

    TextBoxDate.Value = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value
    'TextBoxDate.Value = CDate(TextBoxDate.Value)

    UserName.Visible = False
    UserForm1.UserName.Text = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value

    'If A1 is empty pops up a UserRegister form
    If Len(UserName.Text) = 0 Then
        UserRegister.Show
    End If
    Debug.Print "Name: " & ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
    Debug.Print "Date: " & ThisWorkbook.Worksheets("Sheet1").Range("A2").Value
End Sub

UserForm UserRegister:

Private Sub UserName_Change()
    CommandButtonGO.Enabled = Not (UserName.Text = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value)
End Sub

' I want to store the UserName, so the user does not have to enter it every single time
Private Sub CommandButtonGO_Click()
    ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = Trim(UserName.Text)
    ThisWorkbook.Worksheets("Sheet1").Range("A2").Value = Now
    ThisWorkbook.Save
    Unload Me
End Sub

Private Sub UserRegister_Initialize()
    UserName.Text = UCase(Environ("USERNAME"))
End Sub

Still does not work, Well, I think I will have to find another way. — Veronika Rosales, Sep 02 '15 at 14:48
It works on my Excel 2010 on Win7x64, unless you are storing this data on a different workbook other than the AddIn file. — PatricK, Sep 02 '15 at 23:45

score 0 · Answer 2 · edited May 23 '17 at 11:44

0

Well, I figured out how I can do it.

To get a user name i used a code from here : Getting computer name using VBA (It also says how to get User Name there)

To get current date of entry I just changed the code for output to:

 ActiveCell.Offset(1, 0).Select 'Date column A
 ActiveCell.Value = Date

And it outputs the current date in my LOGfile excel.

Thanks a lot for your help =)

edited May 23 '17 at 11:44

Community

1
1

answered Sep 02 '15 at 18:11

Veronika Rosales

35
1
9

To simply get the Username there is a simpler code: `Application.UserName` – DragonSamu Sep 04 '15 at 09:19

Excel Add-in with some info stored

2 Answers2