Search feature locking screen

[Fire North]

Hello guys,

I'm quite new when it gets to excel/vba so bear with me please.

My excel file is supposed to be a customer database, where the first spreadsheet (called Intro) has a search customer button. My aim here is for the user to write the name of the customer, the macro will then grab the first letter, open the correspondent spreadsheet (e.g. "A","B",...) and search for the first entry that matches the customer's name.

This is what I have got so far (I have the search button opening an userform where the user types the customer's name):

'---------------------------------------------------------

Private Sub CommandButton1_Click()


Dim cname As String
Dim newsheet As String
Dim Rng As Range
Dim objtemp As Range
Dim newworksheet As Worksheet
Dim newactsheet As Worksheet
Dim tempcname As String

Application.ScreenUpdating = False

'Get customer name from Textbox1
cname = TextBox1.Text
'Create new string with just first letter from previous string
newsheet = Left(Trim(cname), 1)
Set newworksheet = ThisWorkbook.Sheets(newsheet)
newworksheet.Activate

'Open sheet with matching letter

Set objtemp = newworksheet.Range("A2:A300")
tempcname = Trim(cname)

If tempcname <> "" Then

With objtemp
Set Rng = .Find(What:=cname, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True

Else
MsgBox "Could Not Find " & cname
Application.Goto (ThisWorkbook.Sheets("Intro").Range("A1"))
End If
End With
Else
MsgBox "Customer Field is empty. Please insert customer's name!"
End If

Application.ScreenUpdating = True
End
End Sub

'-----------------------------------------------------------------------

My issue is that the program does find the customer, but the screen just becomes frozen. The mouse wheel makes the sidebar move, while the screen remains locked for some reason. Another issue is that the delete button or backspace cease to work as well...It seems the only way to fix this is by selecting another spreadsheet. Ideally I wanted to go to the customer's name on the database and edit the next columns (different jobs for the same customer).

My first clue would be an infinite loop, but I am just stuck now (I am sorry for the amount of variables, this was my attempt to fix it without success)... Could anyone please give me a hand here?

Thanks in advance for the attention given to my problem guys.

 

[dmt32]

Hi welcome to the board,</SPAN>

Cannot immediately see why your code should behave in such away – have a try of this slightly modified version & see if any help.</SPAN>

</SPAN>
Private Sub CommandButton1_Click()</SPAN>
 
 
    Dim cname As String</SPAN>
    Dim newsheet As String</SPAN>
    Dim Rng As Range</SPAN>
    Dim objtemp As Range</SPAN>
    Dim newworksheet As Worksheet</SPAN>
    Dim newactsheet As Worksheet</SPAN>
    Dim tempcname As String</SPAN>
 
 
 
    'Get customer name from Textbox1</SPAN>
    cname = TextBox1.Text</SPAN>
 
    If Len(cname) > 0 Then</SPAN>
 
        'Create new string with just first letter from previous string</SPAN>
        newsheet = Left(Trim(cname), 1)</SPAN>
        On Error Resume Next</SPAN>
        Set newworksheet = ThisWorkbook.Sheets(newsheet)</SPAN>
        If Not newworksheet Is Nothing Then</SPAN>
            Application.ScreenUpdating = False</SPAN>
            newworksheet.Activate</SPAN>
 
            'Open sheet with matching letter</SPAN>
 
            Set objtemp = newworksheet.Range("A2:A300")</SPAN>
            tempcname = Trim(cname)</SPAN>
 
            Set Rng = objtemp.Find(What:=cname, _</SPAN>
                                   After:=objtemp.Cells(1, 1), _</SPAN>
                                   LookIn:=xlValues, _</SPAN>
                                   LookAt:=xlWhole, _</SPAN>
                                   SearchOrder:=xlByRows, _</SPAN>
                                   SearchDirection:=xlNext, _</SPAN>
                                   MatchCase:=False)</SPAN>
            If Not Rng Is Nothing Then</SPAN>
                Application.Goto Rng, True</SPAN>
                End</SPAN>
            Else</SPAN>
                MsgBox "Could Not Find " & cname, 48, "Not Found"</SPAN>
                Application.Goto (ThisWorkbook.Sheets("Intro").Range("A1"))</SPAN>
                Me.TextBox1.SetFocus</SPAN>
            End If</SPAN>
        Else</SPAN>
            MsgBox cname & " Worksheet Does Not Exist!", 48, "Not Found"</SPAN>
            Err.Clear</SPAN>
            Me.TextBox1.SetFocus</SPAN>
        End If</SPAN>
 
    Else</SPAN>
        MsgBox "Customer Field is empty. Please insert customer's name!"</SPAN>
        Me.TextBox1.SetFocus</SPAN>
    End If</SPAN>
    Application.ScreenUpdating = True</SPAN>
End Sub</SPAN>

</SPAN>

Dave</SPAN>

 

[Fire North]

Thank you for the welcome and reply Dave. However, the same problem persists with your version...

This issue is completely bizarre and it is driving me bonkers.

Any other ideas guys? Thank you in advance.

 

[Fire North]

I figured out the problem...if anyone is experiencing the same issue, this is what I did.

Go into your userform properties and make sure "ShowModal" is set to false, this will let the form operate when it does not have the focus.

On the same note, Thank you Dave! Your polished version is great!