writing to next row in sheet

[sacrum]

here is the unworking script:

Private Sub Workbook_open()
Worksheets("Frontscreen").Activate
ActiveSheet.Unprotect

Dim x
Set x = CreateObject("WSCRIPT.Network")
Dim u
u = x.UserName
t = Time
'update text box "Last Updated:"
ActiveSheet.TextBoxes("txtLogon").Text = "Welcome to IRIS " & u & " -
Access Time: " & t
ActiveSheet.Protect


lastrow = Worksheets("log").Cells(Rows.Count, "A").End(xlUp).Row
Loglisting = txtLogon.Value

Worksheets("Log").Range("A" & lastrow + 1).Value = Loglisting

End Sub
Basically when workbook opens the current user is identified and displayed
using text box. I want this value to be copied to the next available line
in a log worksheet that is hidden from the user.

 

[Bob Phillips]

Which bit is not working? There is a simpler way to get the user

Private Sub Workbook_open()
Worksheets("Frontscreen").Activate
ActiveSheet.Unprotect

Dim u
u = Environ("Username")
t = Time
'update text box "Last Updated:"
ActiveSheet.TextBoxes("txtLogon").Text = "Welcome to IRIS " & _
u & " - Access Time: " & t
ActiveSheet.Protect

lastrow = Worksheets("log").Cells(Rows.Count, "A").End(xlUp).Row
Loglisting = txtLogon.Value

Worksheets("Log").Range("A" & lastrow + 1).Value = Loglisting

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[sacrum]

lastrow = Worksheets("log").Cells(Rows.Count, "A").End(xlUp).Row
Loglisting = txtLogon.Value

Worksheets("Log").Range("A" & lastrow + 1).Value = Loglisting

End Sub

Its this last bit that causes a run time error - I have created a log
worksheet and the code above is located in ThisWorkbook visible in vb
editor.

 

[Executor]

Hi,

I suggest you use

If IsEmpty(Range("A2")) Then
Sheets("Log").Range("A2").Value = Loglisting
Else
Sheets("Log").Range("A1").End(xlDown).Offset(1, 0).Value =
Loglisting
End If

for the last bit
Where I am presumming that you have a header in cell A1 on sheet Log

Hoop this helps,

Executor

 

[Bob Phillips]

What are all the variable values at that point, lastrow, Loglisting, etc.?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

And you have more responses at your thread in .misc.

 

[sacrum]

What are all the variable values at that point, lastrow, Loglisting, etc.?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

vb runs through (below) but nothing happens in the log sheet - do I need to
do anything in the log sheet....?
Private Sub Workbook_open()
Worksheets("Frontscreen").Activate
ActiveSheet.Unprotect

Dim u
u = Environ("Username")
t = Time
'update text box "Last Updated:"
ActiveSheet.TextBoxes("txtLogon").Text = "Welcome to IRIS " & u & " -
Access Time: " & t
ActiveSheet.Protect

Dim Loglisting


If IsEmpty(Range("A2")) Then
Sheets("Log").Range("A2").Value = Loglisting
Else
Sheets("Log").Range("A1").End(xlDown).Offset(1, 0).Value =
Loglisting
End If

End Sub

 

[Bob Phillips]

which answers none of the questions I asked!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[sacrum]

sorry bob....

this works:

Option Explicit
Private Sub Workbook_open()
Dim x As Object
Dim u As String
Dim t As Date
Dim LastRow As Long

Set x = CreateObject("WSCRIPT.Network")

With Worksheets("FrontScreen")
.Activate
.Unprotect
u = x.UserName
t = Now
'update text box "Last Updated:"
.TextBoxes("txtLogon").Text _
= "Welcome to IRIS " & u & " - Access Time: " _
& Format(t, "hh:mm:ss")
.Protect
End With

With Worksheets("Log")
LastRow = Worksheets("log").Cells(.Rows.Count, "A").End(xlUp).Row
With .Cells(LastRow + 1, "A")
.Value = u
With .Offset(0, 1)
.Value = t
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With
End With

End Sub

**thanks Dave

 

[Dave Peterson]

Sometimes, it gets to be a pain--for both you and the responders--when you post
multiple messages to various newsgroups.

If you really think you have to post to several newsgroups (usually
unnecessary), you can cross post to all at once--just send one message, but send
it to all the newsgroups you want.

It helps you when you're searching for answers and it stops others from
responding to an already answered question.

And it also helps you by having others correct/enhance other suggestions.

 

[sacrum]

Sometimes, it gets to be a pain--for both you and the responders--when you
post
multiple messages to various newsgroups.

If you really think you have to post to several newsgroups (usually
unnecessary), you can cross post to all at once--just send one message,
but send
it to all the newsgroups you want.

It helps you when you're searching for answers and it stops others from
responding to an already answered question.

And it also helps you by having others correct/enhance other suggestions.

hands up it was a mistake Dave.....to busy rushing around.