[RESOLVED] Access object on a subroutine (casting to string??)

[Prokopis]

Hi,
I have two worksheets with names User_Logins and Users_Table.
On User_Logins I have info about last logins of a user and who is the current user (Cell F7)
On Users_Table I have info about all users (first row with data is row10.. A10:A50 usernames , B10:B50 passwords etc)

Also , I have made a userform in order usser to change password (three textboxes)

The problem I have is that I know the specific row (loc.row) searching user but on save button I cant access variable.
I think that loc is defined as object and I can access it as string.

My code is the following

Dim user As Variant
Dim pass As Variant
Dim loc
Dim RangeUsernames As Variant
Dim passPassword As Boolean


Private Sub CancelButton_Click()
    Call UserForm_Initialize
End Sub

Private Sub SaveButton_Click()
    Dim locationNewPass As String
    
	'Checkings here

    If passPassword = True Then
        Msg = MsgBox("Password was successfully validated.", vbInformation, "Change Password")
        Unload Me
    
        'Sheets("Users_Table").Range("B11").Value = "5555"    'it works
        'MsgBox (Sheets("Users_Table").Range("B" & loc.row).Value) 'it does not work
        
        MsgBox loc.row
    End If
   
End Sub

Private Sub UserForm_Initialize()
    oldPassTextBox.Text = ""
    newPassTextBox.Text = ""
    confirmPassTextBox.Text = ""
    oldPassTextBox.SetFocus
 
    
    oldPassTextBox.PasswordChar = "*"
    newPassTextBox.PasswordChar = "*"
    confirmPassTextBox.PasswordChar = "*"
    
    
    passPassword = True
    
    'Find User
    user = Sheets("User_Logins").Range("F7").Value
    
    
    'Define Range of usernames (for instance A10:A50)
    Set WS = Worksheets("Users_Table")
    With WS
        Set LastCellA = .Cells(.Rows.Count, "A").End(xlUp)
    End With

    If LastCellA.row = 10 Then
        RangeUsernames = "A10"
    Else
        RangeUsernames = "A10:A" & LastCellA.row
    End If
    
    'Find row of data on Users_Table
    With Sheets("Users_Table").Range(RangeUsernames)
        Set loc = .Find(What:=user, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False)
            'If Not loc Is Nothing Then
               'MsgBox loc.row
            'End If
    End With
    
    pass = loc.Offset(0, 1)
    
End Sub

Finally, I have a last question
Because my local language on excel is greek, when I am trying the code

 Dim myday As String 
 myday = Format(now(), "dddd")

the result is on greek language

Can I take as result on english ?


Could you help me please ?
Thank you

[westconn1]

change to:- so you know if the user name is not found

            If loc Is Nothing Then
               MsgBox user " not found"
               
            End If

loc.row should work fine if user is found

LookIn:=xlFormulas

try
LookIn:=xlvalues

[Prokopis]

change to:- so you know if the user name is not found

            If loc Is Nothing Then
               MsgBox user " not found"
               
            End If

loc.row should work fine if user is found



try
LookIn:=xlvalues

I changed the code but when I am writing

Sheets("Users_Table").Range("B" & loc.row).Value = newPassTextBox.Value

on Sub SaveButton_Click

I take as result
Run-time error '424':
Object required

[Prokopis]

The probelm is fixed.
Thanks a lot westconn1

Any ideas for date ?

[westconn1]

Any ideas for date ?

as my system is only english i can not test other inbuilt functions, but easy to do with function

Function eday(d as Date) As String
Select Case Weekday(d)
    Case 1: eday = "Sunday"
    Case 2: eday = "Monday"
    Case 3: eday = "Tuesday"
    Case 4: eday = "Wednesday"
    Case 5: eday = "Thursday"
    Case 6: eday = "Friday"
    Case 7: eday = "Saturday"
    
End Select
End Function



'
' call like
Dim myday As String 
 myday = eday(now)

[Prokopis]

Thanks a lot for your help