• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Improvement [SOLVED]

I

ianamck

Member
Hi everyone


VBA is my weakest link and I really struggle with the basics, but I am determined not to let it beat me. I have atable of data which I can search and dispaly the results in a scrollable list. I have written a small piece of code

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'On Error GoTo Exitsub
Dim shp As Shape
Select Case Target.Address
Case Me.Range("KeyWord").Address
Set shp = Me.Shapes("ScrollResults")
With shp
If Me.Range("NbRecFound") > 10 Then
.ControlFormat.Max = Me.Range("NbRecFound") - 10
Else
.ControlFormat.Max = Me.Range("NbRecFound")
End If
End With
Set shp = Nothing
End Select
End Sub
[/pre]

This changes the settings of the scrollbar so if I find 1 record is sets the scrollbar to small values and if I find all records say using * then it increases the values the scroll bar uses.


However I have been trying to write a bit more code that will reset the scrollbar to top rather than leaving it where is last position is or was. That is where I am coming unstuck.


I have posted a sample of my file on Dropbox and would appreciate any improvements or solution to my problem of course if it is possible to do what I am querying.


https://www.dropbox.com/s/hm5n9k5lneyp7gg/Book1.xlsm


Ian M
 
try putting
Code: 
[scrollValue] = 0 at the end of your select:

[pre]Private Sub Worksheet_Change(ByVal Target As Range)
'On Error GoTo Exitsub
Dim shp As Shape
Select Case Target.Address

Case Me.Range("KeyWord").Address
Set shp = Me.Shapes("ScrollResults")
With shp
If Me.Range("NbRecFound") > 10 Then
.ControlFormat.Max = Me.Range("NbRecFound") - 10

Else
.ControlFormat.Max = Me.Range("NbRecFound")
End If
End With
Set shp = Nothing
End Select
[scrollValue] = 0
End Sub
[/pre]
 
Well you cant say people are not helpful on here. Wow that was quick Dave.


Tried your suggestion and it broke everything. VBA crashes workbook and if it stays open long enough when you find more than 10 results the scroll bar doesnt work
 
Hmmm worked fine when i tried it on your example book.


Did you just copy and paste the code?


Does your workbook still have the same named range [scrollValue] as your example and is the scroll bar linked to that same named range?
 
Dave added the line in my code. Saved the code and closed the workbook. Reopened the workbook, enabled macros and checked the code was updated.


Then checked it worked by changing the search value to 5000 which found one record and get debug issues but it show one record which is correct. Change the search value to 500 and again debug issues but 6 records show. Changed to 50 (101 records) and again debug issues and book crashed again. Commented out the new line of code and made sure it still worked by doing the same process as just described and all is fine.


No ranges have changed name
 
Have a look at my file see if it is the same:


https://docs.google.com/file/d/0ByiZqUlQMjxhNXhYa0xBcVozOHM/edit?usp=sharing


Basically all i am doing with [scrollValue] = 0 is setting the cell you have called scrollvalue to 0, thus setting the value of the scroll bar form to 0
 
Looks fine changes the search value to 5000


Run-time error '28' Out of stack space


so clicked end and tried again changing the search value to 50


Run-time error '2147417848(80010108) Method '_Default' of object 'Range' failed.


That then crashes workbook.
 
Thats very strange, all it is doing is changing a value of a cell.


You could change


Code: 
[scrollValue] = 0     


 to      


 Cells(4, 3).Value = 0
 
Dave thank you for your help seems we must be missing something. Tried your new suggestion and ran the same tests. Did exactly the same. Rather than clicking end I debugged and it was the new line of code causing the issue. Fell over exactly the same.


So started from scratch and tried first suggestion did the same. So again started from scratch and tried the second suggestion and almost identical failure.


Even tried writing the code from scratch in the real workbook and did the same. So for sanity tried the file on my laptop and no difference.


Seems a manual setting of the scrollbar is the only solution. Unless someone else has any ideas.


Many thanks for your support it has been much appreciated
 
Hi Dave ,


Include :


Application.EnableEvents = False


Application.EnableEvents = True


in the appropriate places.


Narayan
 
NARAYANK991 thanks for trying to help. But I am simple when it comes to VBA. it took me long enough to figure the code I do have that works. So in short any chance of posting the exact code using your new suggestions.
 
Hi ,


The change is a minor one , but without that you will have the problems you have posted :

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim shp As Shape

Select Case Target.Address
Case Me.Range("KeyWord").Address
Set shp = Me.Shapes("ScrollResults")
With shp
If Me.Range("NbRecFound") > 10 Then
.ControlFormat.Max = Me.Range("NbRecFound") - 10
Else
.ControlFormat.Max = Me.Range("NbRecFound")
End If
End With
Set shp = Nothing
End Select
[scrollValue] = 0
Application.EnableEvents = True
End Sub
[/pre]
This is with reference to Dave's uploaded file Test.xlsm


Narayan
 
Dave and NARAYANK991 well done seems to have solved the crashing


Any chance of explaining why the new lines of code solve the problem or is that a new discovery for me. ;-)
 
Hi ,


These two lines should be used in a Worksheet_Change event procedure , if the procedure is changing any worksheet cell ; if not , what happens is that when a cell is changed , this procedure is triggered once more , which again changes the cell , which re-triggers the procedure , ad infinitum !


By including an Application.EnableEvents = False statement at the beginning , we ensure that any change to a worksheet cell cannot trigger this procedure again , even though a Worksheet_Change has taken place.


Remember to turn it ON again by Application.EnableEvents = True before you exit the procedure.


Narayan
 
Well that explains why I was getting what looked like the target cell flashing very very fast, obviously stuck in a loop until it got upset or I reset the code by clicking End. Well thank you for the help and for the very helpful explanation. Onwards and upwards Will find more out by looking up Worksheet_Change event procedure
 
You must log in or register to reply here.
Back
Top