Solved: Progress Bar help - method not found

Hi friends,
I was trying to implement progress bar for my project. Here is the error I am getting when I am trying to execute the code:
"Sub of function not defined"

I have a method called "FindServersByName". I have defined this in the SearchForm.

I am wondering that should I need to define this in a Module??

Here is the spreadsheet I am attaching. Please advice on progress bar.

Thanks,
Suresh.

Yes, just move "Sub FindServersByName()" to a Module and you won't have this probem.

Hi DRJ,
Thanks somuch for quick response.
I just did that and I am getting the error "Invalid use of Me Key word.
text = Me.SearchTextBox.text
any idea to rectify this bug?

Thanks,
Suresh.

Hi,
I forgot to mention one more thing.
I have over 100 servers and I have 2-300 nodes for each server. The search is taking about 5 minutes. Is there any way to improve the search?
any suggestions are highly appriciate. Thanks in advance.

Thanks,
Suresh.

Quote:

---

Originally Posted by suresh

Hi DRJ,
Thanks somuch for quick response.
I just did that and I am getting the error "Invalid use of Me Key word.
text = Me.SearchTextBox.text
any idea to rectify this bug?

Thanks,
Suresh.

---

Me is invalid because the code is no longer on the form. Substitute the form name in place of Me

[VBA]
text = SearchForm.SearchTextBox.text
[/VBA]

Quote:

---

Originally Posted by suresh

Hi,
I forgot to mention one more thing.
I have over 100 servers and I have 2-300 nodes for each server. The search is taking about 5 minutes. Is there any way to improve the search?
any suggestions are highly appriciate. Thanks in advance.

Thanks,
Suresh.

---

If you add
Application.screenupdating = False
at the head of your code and
Application.screenupdating = True
at the end, this may help.
Regards
MD

Yea, you will have to use the form name instead of Me. If you are searching a lot, and if each search takes a few seconds or more, it is going to take a while.

There are some things that may speed it up. Insead of writing to each cell in the loop, build a range and write one time.

For example
[vba]
Worksheets("MySearch").Range("B8").Offset(i, 0).Value = "Whatever"
[/vba]
Change To
[vba]
If MyRange Is Nothing Then
Set MyRange = Worksheets("MySearch").Range("B8").Offset(i, 0)
Else
Set MyRange = Union(MyRange, _
Worksheets("MySearch").Range("B8").Offset(i, 0))
End If
[/vba]

Then at the end of the loop just change the value of MyRange.
[vba]
MyRange.Value = "Whatever"
[/vba]

Also make sure that there are no events triggering when you are changing the cells. You should start the macro with.

[vba]
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
[/vba]

And set them back to True at the end of your code. Application.ScreenUpdating = False could speed it up considerable, or maybe not. Depending on what is slowing you down. But it will be faster.

If you have a lot of formulas, turn calculations to manual at the start of you code and back to automatic at the end.

[vba]Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic[/vba]

Hi DRJ, mdmackillop and Team,
I dont know how to appriciate you guys.
Earlier to search for 100 records i had to wait for atleast 5 minutes. Now with your advice (Application.EnableEvents = False, Application.DisplayAlerts = False , Application.ScreenUpdating = False , Application.Calculation = xlCalculationManual,Application.Calculation = xlCalculationAutomatic ) I am able to search even 1000 records in less than 5 SECONDS. You guys really just grt at VBA. I am new to this VBA and thanks somuch for your quick help.
One more advice Could you please help me to close (Solved) this thread or should I go a head and continue my next thread in this(I have couple more things in this project)?
Once again thanks to all for help.

Thanks,
Suresh.

Hi Suresh,
"Solved" is not working at the moment, so I've marked it manually.
Glad to be of help.
Regards
MD