To copy vallue from one cell to another on same Sheet1 and update it whenever changes happen.

CT Chua · Mar 27, 2018

Have a login details on Sheet1 as follow

Sheet1

A	B	C	D	E	F



	User Name	password
	Anna	12565
Login	James	65234
Jimmy	Susan	65845
12356	Michael	33654
	Jimmy	12356
	Koda	88751

I have the follow code at the amount

Current Code :-

Option Explicit

Sub CheckUser()
Dim UserRow, SheetCol As Long
Dim SheetNm As String
With Sheet8
.Calculate

If .Range("A10").Value = Empty Then 'Incorrect Username
MsgBox "Please enter correct user name"
Exit Sub
End If

If .Range("A9").Value <> True Then 'incorrect password
MsgBox "Please enter correct password"
Exit Sub

End If
Range("A7").Copy Range("A1") 'copy Username to A1

LoginForm.Hide 'Hide Login Form
UserRow = .Range("A10").Value ' User Row
.Range("A7,A8").ClearContents

How can i capture the username in A7 and place it at A1. A1 will have to update with new username whenever a new user sign in.
I have try adding this code to the existing code but it didnt work properly.

range ("A7").copy range ("A1")

Would appreciate if anyone can help me out on this.

Thanks

louisH · Mar 27, 2018

Hi.

Why not :

Code:

Range("A1").Value = Range("A7").Value

My Aswer Is This · Mar 27, 2018

Are you having all users entering there name and Password in the same cell?

For example Name in A10 Password A11
And the script needs to see if name matches password by looking Down column B and C

CT Chua · Mar 27, 2018

Hi,
First I will try the code from louisH when I get home and Thanks louisH.

To answer "My Answer Is This" -
Yes, I have a userform with the username link to A7 and Password link to A8. Yes, the script needs to check if name matches password in Column B and C.
and the userform is clear of username and password and ready for next signin.

Thanks all for the help.

Regards,

CT Chua · Mar 27, 2018

Hello LouisH,

I replace mine addon code

range ("A7").copy range ("A1")

with yours below
Range("A1").Value = Range("A7").Value

Result is as follow

On initial log-in with username "Jimmy" both code capture and place "Jimmy" on A1.
After logging off "Jimmy" and then re-login with another User name. A1 still show "Jimmy", new username did not replace "Jimmy" in A1.

Thanks.

louisH · Mar 27, 2018

CT Chua said:
On initial log-in with username "Jimmy" both code capture and place "Jimmy" on A1.

What do you mean by "both code" ? Do you have any other macro ?

CT Chua said:
After logging off "Jimmy" and then re-login with another User name. A1 still show "Jimmy", new username did not replace "Jimmy" in A1

I see you are doing :

Code:

(With Sheet8)
.Range("A7,A8").ClearContents

So you're clearing A7 & A8 from Sheet8, but from what I understand you want to clear login & psw from the active sheet (sheet1) right ? :

Code:

Range("A7,A8").ClearContents

I was guessing it's not clearing the contents of the right cell so "Jimmy" was still in Range A7 from the login sheet.
Check if you have any "." where you don't want them & vice versa. Otherwise i don't see why it wouldn't replace A1 with the new value when you run the macro.

CT Chua · Mar 31, 2018

Hi louisH,

Sorry for the confusion,
Range("A7").Copy Range("A1") 'copy Username to A1 (my code)
[FONT=&quot]Range("A1").Value = Range("A7").Value (our’s Code)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I tested these two code and both working fine. Placing [/FONT]
[FONT=&quot]“Jimmy” in A1 as expected.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]What both code cannot do is when “Jimmy” logout and say “Anna” login.[/FONT]
[FONT=&quot]A1 do not capture “Anna”. A1 still show “Jimmy”.[/FONT]
[FONT=&quot] [/FONT]
Range("A7,A8").ClearContents – A7 & A8 will be clear on closing workbook. Purpose is to prevent login form from showing Username and Password on next login. A7 contain username, A8 user password. A9 and A10 are lookup to check and match user and password. [FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Just a thought here, maybe I need to clear A1 “Jimmy” when he logout and[/FONT]
[FONT=&quot]free A1 for next login. [/FONT]
[FONT=&quot]If that the case, how should the code be? Would appreciate some help on it.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]In the meantime, I would double check my current code for error.[/FONT]
[FONT=&quot]Greatly appreciate your assistance and Thanks.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Regards,[/FONT]
[FONT=&quot]P.S: Sheet8 is a test sheet on another workbook.[/FONT]

To copy vallue from one cell to another on same Sheet1 and update it whenever changes happen.

CT Chua

New Member

Excel Facts

louisH

Board Regular

My Aswer Is This

Well-known Member

CT Chua

New Member

CT Chua

New Member

louisH

Board Regular

CT Chua

New Member

Forum statistics

Share this page

To copy vallue from one cell to another on same Sheet1 and update it whenever changes happen.

CT Chua

New Member

Excel Facts

louisH

Board Regular

My Aswer Is This

Well-known Member

CT Chua

New Member

CT Chua

New Member

louisH

Board Regular

CT Chua

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock