Can't Scroll to Field Automatically when using Find Macro

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
164
Hi;

I'm trying to modify this macro to automatically scroll to the active field when the value I select from a list in a combobox is used. The cursor will o to the right field, but the focus is not set to have the scroll automatically active to bring it down to the right position. Macro is posted below:

Sub SearchProducts()
Dim searchValue
Dim counter As Integer, sheetCount As Integer
Dim startSheet, startCell
Application.ScreenUpdating = False
On Error Resume Next
startCell = ActiveCell.Address
startSheet = ActiveSheet.Name
searchValue = ActiveSheet.ComboBox1.Value
If searchValue = "" Then Exit Sub
If IsError(CDbl(searchValue)) = False Then searchValue = CDbl(searchValue)
sheetCount = ActiveWorkbook.Sheets.Count
counter = 1
Do Until counter > sheetCount
Sheets(counter).Activate
Cells.Find(What:=searchValue, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Activate
If ActiveCell.Value = searchValue Then Exit Do
counter = counter + 1
Loop
If ActiveCell.Value <> searchValue Then
MsgBox "The value " & Chr(34) & searchValue & Chr(34) & " was not found.", vbExclamation + vbOKOnly, "Data not found!"
Application.Goto Reference:=Range(ActiveSheet.ActiveCell)
Application.ScreenUpdating = True
Exit Sub
End If
Application.ScreenUpdating = True
Application.Goto Reference:=ActiveSheet.ActiveCell.Range("activecell").Select, Scroll:=True
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target _
As Range, Cancel As Boolean)
Cancel = True
If ActiveSheet.ScrollArea = "" Then
ActiveSheet.ScrollArea = "range1"
Else
ActiveSheet.ScrollArea = ""
End If
End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Shouldn't:

Application.Goto Reference:=ActiveSheet.ActiveCell.Range("activecell").Select, Scroll:=True

be?

Application.Goto Reference:=ActiveCell, Scroll:=True
 

Forum statistics

Threads
1,136,700
Messages
5,677,277
Members
419,683
Latest member
MrVBAConfused

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