How to Fill a String Array
 

I have a userform that pops up when a workbook is opened. The sheets and the
workbook is hidden. Depending on who signs in determines which sheets are
viewable and what information gets put on the main page a.k.a. "QUOTES". For
some reason I am getting a Type Mismatch Error on the first line I try to
assign some values to the first parts of the Array. Does anyone know why? I
am not that good with Arrays and how they work so if someone could explain I
would greatly appreciated it!!

Option Explicit
Option Base 1

Private Sub UserForm_Initialize()

With cboUserName
.AddItem "Todd Heller"
.AddItem "Peter Maida"
.AddItem "Joe Livatino"
.AddItem "Vanessa Schnurr"
.AddItem "Kyla Godden"
.AddItem "Nancy Eason"
.AddItem "Rick Wanser"
.AddItem "Administrator"
End With

End Sub

Private Sub cmbLogin_Click()

Dim aryUserInfo(7, 1, 1) As String
Dim n As Long
Dim bolVisible As Boolean
Dim lngSheetIndex As Long
Dim i As Long

' ensure the user selects a username and enters a password
If IsEmpty(cboUserName) Or IsEmpty(tbxPassword) Then
MsgBox "How do you expect me to log you in without an UserName &
Password?", vbCritical
Exit Sub
End If

' fill array user password, name & extension, e-mail
aryUserInfo(1, 1, 1) = Array("toddh1", "Todd Heller @ Ext. 207",
"E-mail: ")
aryUserInfo(2, 1, 1) = Array("peterm2", "Peter Maida @ Ext. 208",
"E-mail: ")
aryUserInfo(3, 1, 1) = Array("joel3", "Joe Livatino @ Ext. 220",
"E-mail: ")
aryUserInfo(4, 1, 1) = Array("kylag4", "Kyla Godden @ Ext. 205",
"E-mail: ")
aryUserInfo(5, 1, 1) = Array("vanessas6", "Vanessa Schnurr @ Ext. 203",
"E-mail: ")
aryUserInfo(6, 1, 1) = Array("nancye10", "Nancy Eason @ Ext. 209",
"E-mail: ")
aryUserInfo(7, 1, 1) = Array("rickw12", "Rick Wanser @ Ext. 212",
"E-mail: ")
aryUserInfo(8, 1, 1) = Array("ryanh7", "Administrator", "")

' get index number or user combobox
n = cboUserName.ListIndex

' determine if sheets are show or not
Select Case n
Case Is = 7
bolVisible = True
lngSheetIndex = 1
Case 0 To 4
bolVisible = False
lngSheetIndex = 2
Case 5 To 6
bolVisible = False
lngSheetIndex = 3
End Select

With ThisWorkbook

' hide or unhide sheets
If tbxPassword = aryUserInfo(n, 0, 0) Then
.Unprotect "AdTech"
For i = .Sheets.Count To lngSheetIndex Step -1
.Sheets(i).Visible = bolVisible
Next i
.Protect "AdTech"
Else
MsgBox "You have entered an incorrect password. Try agian.",
vbCritical, "Problem"
With tbxPassword
.Value = ""
.SetFocus
End With
Exit Sub
End If

' add user information to Quote sheet
With .Sheets("QUOTE")
.Unprotect "AdTech"
.Range("H6") = aryUserInfo(n, 1, 0)
.Range("H7") = aryUserInfo(n, 0, 1)
.Protect "AdTech"
End With

' show the workbook
Windows(.Name).Visible = True
End With

Unload Me

End Sub
--
Cheers,
Ryan

How to Fill a String Array
 

Your array is defined incorrectly... You need a 2 dimensional array (7 x 3)

Dim aryUserInfo(7, 3) As String

aryUserInfo(1, 1) = "toddh1"
aryUserInfo(1, 2) = "Todd Heller @ Ext. 207"
aryUserInfo(1, 3) = "E-mail: "
'...

Or I would be more inclined to do an array of user defined types but that is
just my preference...

'In a standard code module
Public Type Person
strName as string
strPhone as string
strEMail as string
end type

'Where ever you want
dim aryUserInfo(7) as Person

aryUserInfo(1).strName= "toddh1"
aryUserInfo(1).strPhone "Todd Heller @ Ext. 207"
aryUserInfo(1).strEMail = "E-mail: "

--
HTH...

Jim Thomlinson


"RyanH" wrote:

I have a userform that pops up when a workbook is opened. The sheets and the
workbook is hidden. Depending on who signs in determines which sheets are
viewable and what information gets put on the main page a.k.a. "QUOTES". For
some reason I am getting a Type Mismatch Error on the first line I try to
assign some values to the first parts of the Array. Does anyone know why? I
am not that good with Arrays and how they work so if someone could explain I
would greatly appreciated it!!

Option Explicit
Option Base 1

Private Sub UserForm_Initialize()

With cboUserName
.AddItem "Todd Heller"
.AddItem "Peter Maida"
.AddItem "Joe Livatino"
.AddItem "Vanessa Schnurr"
.AddItem "Kyla Godden"
.AddItem "Nancy Eason"
.AddItem "Rick Wanser"
.AddItem "Administrator"
End With

End Sub

Private Sub cmbLogin_Click()

Dim aryUserInfo(7, 1, 1) As String
Dim n As Long
Dim bolVisible As Boolean
Dim lngSheetIndex As Long
Dim i As Long

' ensure the user selects a username and enters a password
If IsEmpty(cboUserName) Or IsEmpty(tbxPassword) Then
MsgBox "How do you expect me to log you in without an UserName &
Password?", vbCritical
Exit Sub
End If

' fill array user password, name & extension, e-mail
aryUserInfo(1, 1, 1) = Array("toddh1", "Todd Heller @ Ext. 207",
"E-mail: ")
aryUserInfo(2, 1, 1) = Array("peterm2", "Peter Maida @ Ext. 208",
"E-mail: ")
aryUserInfo(3, 1, 1) = Array("joel3", "Joe Livatino @ Ext. 220",
"E-mail: ")
aryUserInfo(4, 1, 1) = Array("kylag4", "Kyla Godden @ Ext. 205",
"E-mail: ")
aryUserInfo(5, 1, 1) = Array("vanessas6", "Vanessa Schnurr @ Ext. 203",
"E-mail: ")
aryUserInfo(6, 1, 1) = Array("nancye10", "Nancy Eason @ Ext. 209",
"E-mail: ")
aryUserInfo(7, 1, 1) = Array("rickw12", "Rick Wanser @ Ext. 212",
"E-mail: ")
aryUserInfo(8, 1, 1) = Array("ryanh7", "Administrator", "")

' get index number or user combobox
n = cboUserName.ListIndex

' determine if sheets are show or not
Select Case n
Case Is = 7
bolVisible = True
lngSheetIndex = 1
Case 0 To 4
bolVisible = False
lngSheetIndex = 2
Case 5 To 6
bolVisible = False
lngSheetIndex = 3
End Select

With ThisWorkbook

' hide or unhide sheets
If tbxPassword = aryUserInfo(n, 0, 0) Then
.Unprotect "AdTech"
For i = .Sheets.Count To lngSheetIndex Step -1
.Sheets(i).Visible = bolVisible
Next i
.Protect "AdTech"
Else
MsgBox "You have entered an incorrect password. Try agian.",
vbCritical, "Problem"
With tbxPassword
.Value = ""
.SetFocus
End With
Exit Sub
End If

' add user information to Quote sheet
With .Sheets("QUOTE")
.Unprotect "AdTech"
.Range("H6") = aryUserInfo(n, 1, 0)
.Range("H7") = aryUserInfo(n, 0, 1)
.Protect "AdTech"
End With

' show the workbook
Windows(.Name).Visible = True
End With

Unload Me

End Sub
--
Cheers,
Ryan