VB CODING ERROR

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

fridgenep

Board Regular
Joined
Mar 26, 2006
Messages
73
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:

Code:
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
The username and current date/time appear because that's how it is written:

Code:
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.
 

Forum statistics

Threads
1,137,366
Messages
5,681,072
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top