Solved: Runtime Error 1004 for Application.Username

[Phelony]

Hi guys

I'm trying to build a change history log into the rear end of a spreadsheet and seem to having some issues. :help

This worked perfectly until I started getting the code to hide and unhide sheets, although I've tried this in several combinations and still seem to be stuck. :wot

When the user closes excel, the following code should run:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Change History").Visible = xlSheetVisible
UserNameOffice = Application.UserName
Sheets("Change History").Activate
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("$A$2") = UserNameOffice
Range("$B$2") = Date
Range("$C$2") = Time
Range("$D$2") = Sheets("New Record").Range("$I$2")
Range("$E$2") = Sheets("Review Existing Record").Range("$D$4")

There then follows a series of 15 sheets being hidden that I won't bore you with

End sub

I'm getting an error on Range("$A$2") = UserNameOffice (line 7) and I don't seem to be able to figure out why.
:mkay

Any ideas?

Thanks

Phel x

[Phelony]

I've taken the code to close the change history log out of the workbook close code and this seems to work fine now...no idea why. :bug:

[Phelony]

Further update:

It's now doing exactly what I want, but it's still throwing up an error despite the fact that it's carried out the same action it's complaining about! *sob*

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
UserNameOffice = Application.UserName
Sheets("Change History").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("$A$2") = UserNameOffice
Range("$B$2") = Date
Range("$C$2") = Time
Range("$D$2") = Sheets("New Record").Range("$I$2")
Range("$E$2") = Sheets("Review Existing Record").Range("$D$4")

If anyone could point me in the direction that this madness has come from it would be greatly apprecaited!

: pray2:

Phel x

[Simon Lloyd]

Try using this:


environ("username")
it picks up the windows login name, as it may be that the user hasn't set an application username!

[phendrena]

You could also force the user to set the username, for example :


Dim strUserName as string

If Application.Username = "" then
strUserName = Inputbox ("Please set your username")
Application.Username = strUserName
End If


Or you could just set it automatically to match the windows login name :


If Application.Username = "" then
application.username = environ("username")
end If

[Phelony]

They should have by default, however, I've changed it but am still getting the 1004. Have I implimented your suggested change incorrectly?

Application.ScreenUpdating = False
UserNameOffice = Environ("username")
Sheets("Change History").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("$A$2") = UserNameOffice

I've never used that function before so I probably haven't! :dunno

Thank you for your help though

Phel x

[Phelony]

Hold on, I was wrong. It's not the username it's objecting to, its the input commands:

Range("$A$2") = Environ("username")
Range("$B$2") = Date
Range("$C$2") = Time
Range("$D$2") = Sheets("New Record").Range("$I$2")
Range("$E$2") = Sheets("Review Existing Record").Range("$D$4")

Once the username was sorted out, it moved on to find an error with the date field and I have a feeling that it's going to systematically query each of these...:banghead:

Not entirely sure what the issue is although I can do the date and time with formulae instead of coding.

Time to play around with it again methinks!

But thank you for the help and guidance on the username issue, it solved another problem I was having! :bow:

Thank you.

Phel x

[Phelony]

On Error Resume Next

And it works fine....everything is populating as it should and there are no apparent side effects...very curious.

I'd be interested if anyone could tell me what it's objecting to, but for now, problem solved. Kinda.

[Simon Lloyd]

You shouldn't use On Error Resume next as it masks all errors, there is absolutely nothing wrong with your code, i suspect that your code to hide sheets is causing the problem!

[Phelony]

Other than a few changes for DPA purposes, this is the code as it stands. With out the "On Error Resume Next" it stalls and throws up issues left right and center, but it has executed the code in full at the time it reports an error. This is what's confusing me. To all intents and purposes the issues that it reckons exist are not there as it's done what was expected...

...without simply ignoring the error I'm at a loss what to do.

Each line of this is being executed correctly, but it's complaining endlessly about it. :dunno


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Application.UserName = Environ("username")
UserNameOffice = Environ("username")
Sheets("Change History").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("$A$2") = Environ("username")
Range("$B$2") = Date
Range("$C$2") = Time
Range("$D$2") = Sheets("New Record").Range("$I$2")
Range("$E$2") = Sheets("Review Existing Record").Range("$D$4")
'clear contents code (this section was done as a straight recorded macro as it was
'quicker than mapping the cells - so far this has presented no issues)
Sheets("Review Existing Record").Select
Range( _
"E2:F2,D4:E4,D5:E5,D7:G7,C9:E9,D11:F11,D13:J13,D15:K21,K7:L7,K8:L8,I11:K11,J 4:K4, _
J5:K5,K23:L23,J25:L25,D23:E23,D25,F25,D27:E27,I27:J27,H29:J29,J31:K31,D29:E 29,D33:J36,_
D38:J41,H44:J47,E43:F43,D47:F47" _
).Select
Range("D47").Activate

Selection.clearcontents
Sheets("New Record").Select

Range( _
"I2:J2,J35,D30,D28,I28:J28,I26:J26,H24:J24,D24,D26,D16:K22,D14,I14,D12,E10:F 10,C8:F8,_
H6:J6,H4:J4,C4:D4,C2:D2" _
).Select
Range("C2").Activate
Selection.clearcontents

Sheets("Record Reference Finder").Select
Range("C4").Select
Selection.clearcontents

'renew "review existing record" formulae
'temporarily removed to find formulae reintegration solution - merge cells revamp?

'hide all unrequired sheets

Sheets("FIM Complaint Database").Visible = False
Sheets("MEA Complaint Database").Visible = False
Sheets("Booking Center Complaints").Visible = False
Sheets("FSD Complaint Database").Visible = False
Sheets("UK GMT Complaint Database").Visible = False
Sheets("Master Database").Visible = False
Sheets("ORM Complaint Dashboard").Visible = False
Sheets("Review Mapping").Visible = False
Sheets("ORM Complaints").Visible = False
Sheets("Security").Visible = False
Sheets("Drop Down Menus").Visible = False
Sheets("New Record").Visible = False
Sheets("Review Existing Record").Visible = False
Sheets("Front Screen").Select

End Sub


Ideally, I wouldn't ignore the errors for the exact reason you've stated, however, I don't really see a way around it as there is, as you've said, no error...confusing to say the least!

If you've got any insights they would be greatly appreciated.

Phel x

PS - I apologise for the roughness of the code that's here, there are cleaner ways to acheive some of this however I'm keeping it longwinded while I'm building and will clean it up at a later point. - That's the plan anyway!

[Simon Lloyd]

this seems to work although i didn't create the sheets:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Application.UserName = Environ("username")
UserNameOffice = Environ("username")
Sheets("Change History").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("$A$2") = Environ("username")
Range("$B$2") = Date
Range("$C$2") = Time
Range("$D$2") = Sheets("New Record").Range("$I$2")
Range("$E$2") = Sheets("Review Existing Record").Range("$D$4")
'clear contents code (this section was done as a straight recorded macro as it was
'quicker than mapping the cells - so far this has presented no issues)
Sheets("Review Existing Record").Range("E2:F2,D4:E4,D5:E5,D7:G7,C9:E9,D11:F11,D13:J13,D15:K21,K7:L7,K8:L8,I11:K11,J 4:K4," & _
"J5:K5,K23:L23,J25:L25,D23:E23,D25,F25,D27:E27,I27:J27,H29:J29,J31:K31,D29:E 29,D33:J36,D38:J41," & _
"H44:J47,E43:F43,D47:F47").ClearContents

Sheets("New Record").Range("I2:J2,J35,D30,D28,I28:J28,I26:J26,H24:J24,D24,D26,D16:K22,D14,I14,D12,E10:F 10,C8:F8," & _
"H6:J6,H4:J4,C4:D4,C2:D2").ClearContents

Sheets("Record Reference Finder").Range("C4").ClearContents

'renew "review existing record" formulae
'temporarily removed to find formulae reintegration solution - merge cells revamp?

'hide all unrequired sheets
Sheets(Array("FIM Complaint Database", "FIM Complaint Database", "Booking Center Complaints", "FSD Complaint Database", _
"UK GMT Complaint Database", "Master Database", "ORM Complaint Database", "Review Mapping", "ORM Complaints", "Security", _
"Drop Down Menus", "New Record", "Review Existing Record")).Visible = xlSheetHidden

Sheets("Front Screen").Select

End Sub

[Phelony]

Wow, that really does seem to have fixed it without ignoring the problem!

Perhaps I should have tidied earlier!!

Thanks Simon, you've been a great help.

Phel x

[Simon Lloyd]

Glad thats sorted for you!