Type mismatch Error when initializing variables

Question

I am having this really strange error in excel VBA that keeps showing up. I used the codes below

Public psword, oldpsword As Integer


Public Sub Auto_Open()

psword = Sheets("Main Menu").Cells(55, 1)
oldpsword = psword
For Each ws In Worksheets
    ws.Protect Password:=psword
Next ws

End Sub

Whenever the code goes to the line "oldpsword = psword" an error message pops up showing Run-time '13:' type mismatch error. How can i fix it?

In line Public psword, oldpsword As Integer only oldpsword is Integer while psword is Variant. Redim it Public psword As integer, oldpsword As Integer — Dmitry Pavliv
– Dmitry Pavliv, Commented May 2, 2014 at 12:08
I agree with simoco that you need to fix your Dim statement. However, I suspect the problem is that Cells(55,1) does not contain an integer. A variant can contain anything so psWord = ... is OK but oldpsword = psword fails because the value in psword cannot be converted to a integer. — Tony Dallimore
– Tony Dallimore, Commented May 4, 2014 at 19:42

Gary's Student · Accepted Answer · 2014-05-02 12:12:51Z

2

Try replacing:

Public psword, oldpsword As Integer

with

Dim psword as Long, oldpsword as Long

answered May 2, 2014 at 12:12

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Wagner · Accepted Answer · 2014-05-02 12:46:51Z

0

Just a general comment here as simoco and Gary's Student have got you laced up... If your passwords might be alphanumeric (a little foresight here might save you debug hours in the future, especially if your client is getting the passwords wrong haha), then you may want to use a combination of variant and the CStr (http://www.techonthenet.com/excel/formulas/cstr.php):

Public psword As Variant, oldpsword As Variant

Public Sub Auto_Open()

psword = ThisWorkbook.Sheets("Main Menu").Cells(55, 1)
oldpsword = psword
For Each ws In Worksheets
    ws.Protect Password:=CStr(psword)
Next ws

End Sub

answered May 2, 2014 at 12:46

Dan Wagner

2,7232 gold badges16 silver badges19 bronze badges

Collectives™ on Stack Overflow

Type mismatch Error when initializing variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related