initials?

[Smoakstack]

here is my code

Public Sub Workbook_Open()
Dim nam As String

If Sheet2.Range("Z1") = "" Then

'Input of name
nam = InputBox("Enter your name.", "Summary", "Name")

'Unlock Information
Sheet1.Unprotect Password:=""
Sheet2.Unprotect Password:=""
Sheet3.Unprotect Password:=""
Sheet4.Unprotect Password:=""
Sheet5.Unprotect Password:=""
Sheet6.Unprotect Password:=""
Sheet7.Unprotect Password:=""
Sheet8.Unprotect Password:=""
Sheet9.Unprotect Password:=""
Sheet10.Unprotect Password:=""

If nam = "" Then
Sheets("SUMMARY").Range("A3").Value = "Name"
Else
Sheets("SUMMARY").Range("A3").Value = nam
End If
End if

'Other code below

End Sub

I would like Range A3 to be the initials of the variable nam?

Is there a way to do this?

 

[MrKowz]

Try:

Public Sub Workbook_Open()
Dim nam As String, _
    i   As Long
If Sheet2.Range("Z1") = "" Then
    'Input of name
    nam = InputBox("Enter your name.", "Summary", "Name")
    
    'Unlock Information
    For i = 1 To 10
        Sheets(i).Unprotect Password:=""
    Next i
    
    If InStr(nam, " ") <> 0 And Len(nam) > 0 Then
        Sheets("SUMMARY").Range("A3").Value = UCase$(Left$(nam, 1)) & UCase$(Mid(nam, InStr(nam, " ") + 1, 1))
    ElseIf Len(nam) > 0 Then
        Sheets("SUMMARY").Range("A3").Value = nam
    Else
        Sheets("SUMMARY").Range("A3").Value = "Name"
    End If
End If
End Sub

 

[Smoakstack]

Subscript out of range starting as soon as code hits this line

if instr(nam, " ") <> 0 and len(nam)>0

?

 

[Smoakstack]

bump

Anyone have any ideas?

here is the code.

If nam = "" Then
Sheets("SUMMARY").Range("A3").Value = "Estimator"
Else
Sheets("SUMMARY").Range("A3").Value = UCase$(Left$(nam, 1)) & UCase$(Mid(nam, InStr(nam, " ") + 1, 1))
End If

Subscript out of range happens on both cases

 

[MrKowz]

Do you have a worksheet named "Summary"?

 

[Smoakstack]

yes, but in caps. "SUMMARY"

 

[MrKowz]

It isn't case sensitive.

Try running the following macro to see if it errors:

Public Sub foo()
Sheets("Summary").Range("A3").Value = 1
End Sub

 

[Smoakstack]

No. It does not error.

 

[MrKowz]

Can you please copy over the entire code you are using? I had placed the if instr(nam, " ") <> 0 and len(nam)>0 line in there so that the code would not error if someone entered only their first or last name (ie there would be no space).

 

[Smoakstack]

got it to work. Had to erase your password For lines and replace back to existing (?)

Public Sub Workbook_Open()
Dim nam As String
Dim bid As String
Dim Job As String
Dim loc As String
Dim Reply As String
Dim i As Long
Dim InStr As String

If Sheet2.Range("Z1") = "" Then
'Input of Estimators' Name
nam = InputBox("Enter your name.", "Summary", "Estimator")
'Unlock Information
Sheet1.Unprotect Password:=""
Sheet2.Unprotect Password:=""
Sheet3.Unprotect Password:=""
Sheet4.Unprotect Password:=""
Sheet5.Unprotect Password:=""
Sheet6.Unprotect Password:=""
Sheet7.Unprotect Password:=""
Sheet8.Unprotect Password:=""
Sheet9.Unprotect Password:=""
Sheet10.Unprotect Password:=""
OSTExport20.Unprotect Password:=""


If InStr(nam, " ") <> 0 And Len(nam) > 0 Then
Sheets("SUMMARY").Range("A3").Value = UCase$(Left$(nam, 1)) & UCase$(Mid(nam, InStr(nam, " ") + 1, 1))
ElseIf Len(nam) > 0 Then
Sheets("SUMMARY").Range("A3").Value = nam
Else
Sheets("SUMMARY").Range("A3").Value = "Name"
End If

'Other Code here

End Sub