first letter of text box referece

C

camron107

i have created a user input form in VBA, which works fine.

"organisation" is a field on the form

if a user enters an organisation beggining with letters A-F for example, i want the data entered to go into sheet 1. if the organisation begins with G-L, i want it to go into sheet 2 etc...

i have managed such a formula in excel, but does not work in VB.

my current attempt looks like this:

If Organisation.Value(Left >= CHAR(65), Left <= CHAR(70)) Then

ActiveWorkbook.Sheets("sheet1").Activate

Range("A2").Select

ElseIf Organisation.Value(Left >= CHAR(71), Left <= CHAR(76)) Then

ActiveWorkbook.Sheets("Sheet2").Activate

Range("A2").Select

Thank you for your help and time, it is much appreciated.
 
D

Dave Peterson

One way:

Option Explicit
Private Sub CommandButton1_Click()

Dim wks As Worksheet

Select Case Asc((LCase(Left(organization.Value, 1))))
Case Asc("a") To Asc("f"): Set wks = Worksheets("Sheet1")
Case Asc("g") To Asc("l"): Set wks = Worksheets("sheet2")
Case Else: Set wks = Nothing
End Select

If wks Is Nothing Then
'some error message
Else
'do what you want
End If

End Sub
 
C

camron107

Thank you for your help I am almost there, my syntax is as follows:

Select Case Asc((LCase(Left(Organisation.Value, 1))))

Case Asc("a") To Asc("f"): ActiveWorkbook.Sheets("Organisations A-F").Activate Range("A2").Select

Case Asc("g") To Asc("l"): ActiveWorkbook.Sheets("Organisations G-L").Activate Range("A2").Select

Case Asc("m") To Asc("r"): ActiveWorkbook.Sheets("Organisations M-R").Activate Range("A2").Select

Case Asc("s") To Asc("z"): ActiveWorkbook.Sheets("Organisations S-Z").Activate Range("A2").Select

End Select
-----------
For some reason, if the entry is A-F it works fine, however when G-Z is entered, excel freezes and i lose all my work!

Do I need an 'IF' function somewher?

cheerz for your help
 
D

Dave Peterson

I don't have a guess why it locks up excel. I could understand it if you typed
in an organization name that didn't match you conditions.

But even if you have a typo in your worksheet name, excel shouldn't crash.

So save your work and try this:

Option Explicit
Private Sub CommandButton1_Click()

Dim wksName As String
Dim testWks As Worksheet

Select Case Asc((LCase(Left(Organisation.Value, 1))))
Case Asc("a") To Asc("f"): wksName = "Organisations A-F"
Case Asc("g") To Asc("l"): wksName = "Organisations G-L"
Case Asc("m") To Asc("r"): wksName = "Organisations M-R"
Case Asc("s") To Asc("z"): wksName = "Organisations S-Z"
Case Else: wksName = ""
End Select

Set testWks = Nothing
On Error Resume Next
Set testWks = ActiveWorkbook.Worksheets(wksName)
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Invalid Name"
Else
Application.Goto ActiveWorkbook.Worksheets(wksName).Range("a2"), _
Scroll:=True
End If

End Sub

If it still crashes excel, try the same code in a brand new workbook. If it
works there, I don't have another guess.
 
C

camron107

cheerz dave, much appreciated

Dave Peterson said:
I don't have a guess why it locks up excel. I could understand it if you typed
in an organization name that didn't match you conditions.

But even if you have a typo in your worksheet name, excel shouldn't crash.

So save your work and try this:

Option Explicit
Private Sub CommandButton1_Click()

Dim wksName As String
Dim testWks As Worksheet

Select Case Asc((LCase(Left(Organisation.Value, 1))))
Case Asc("a") To Asc("f"): wksName = "Organisations A-F"
Case Asc("g") To Asc("l"): wksName = "Organisations G-L"
Case Asc("m") To Asc("r"): wksName = "Organisations M-R"
Case Asc("s") To Asc("z"): wksName = "Organisations S-Z"
Case Else: wksName = ""
End Select

Set testWks = Nothing
On Error Resume Next
Set testWks = ActiveWorkbook.Worksheets(wksName)
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Invalid Name"
Else
Application.Goto ActiveWorkbook.Worksheets(wksName).Range("a2"), _
Scroll:=True
End If

End Sub

If it still crashes excel, try the same code in a brand new workbook. If it
works there, I don't have another guess.
Click to expand...
 
Top