Search feature locking screen

Fire North

New Member
Joined
Feb 12, 2014
Messages
5
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
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>

Code:
</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

New Member
Joined
Feb 12, 2014
Messages
5
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

New Member
Joined
Feb 12, 2014
Messages
5
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!
 

Forum statistics

Threads
1,141,049
Messages
5,703,932
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top