VB CODING ERROR

[mrbeanyuk]

Sorry to be so vague in the subject but I have a code that requests a username when the spreadsheet first logs on.

The problem is that in column A of sheet1, the username and some random dates appear.

Wierd!

First Code:

Private Sub Workbook_Open()
Dim user_name As String
Dim FBR As Long
user_name = InputBox("Please enter your ISIS username to access the Project HERMES interface. Please note that this spreadsheet should not be amended in any way.", "PROJECT HERMES LOGIN", "")
If user_name <> "muirheadn110" Then
ActiveWorkbook.Close (0)
End If
With Sheets("Homepage")
FBR = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(FBR, 2) = user_name
.Cells(FBR, 1) = Now
End With
End Sub

Second Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$J$30" And Target.Address <> "$J$30" Then Exit Sub
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Homepage" Then
With sht.UsedRange
Set c = .Find(Target.Value, lookat:=xlWhole)
If Not c Is Nothing Then
sht.Activate
c.Select
Exit Sub
End If
End With
End If
Next
End Sub

 

[fridgenep]

Hi,

I hope understanding you in the right way.

If you don´t want to have the username and the Date + Time in Column A of sheet "Homepage" (I think that´s sheet1, isn´t it?) then delete this code from Code1:

With Sheets("Homepage") 
FBR = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 
.Cells(FBR, 2) = user_name 
.Cells(FBR, 1) = Now 
End With

 

[Von Pookie]

The username and current date/time appear because that's how it is written:

With Sheets("Homepage")
FBR = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(FBR, 2) = user_name
.Cells(FBR, 1) = Now
End With

This tells the code to put the username that was entered in column B, and the current date/time in column A.