dragonmist
New Member
- Joined
- Mar 22, 2017
- Messages
- 5
- Office Version
- 2016
- Platform
- Windows
I am currently working on a spreadsheet for tracking jobs where I work. I added a combobox to this sheet to make it easier to locate by job number.
The job numbers are located in Column A on the sheet.
The combobox worked fine until I discovered that when I was no longer working with the combobox and I was entering something in column A
and tried to tab over to the next column, the sheet scrolled to the cell of whatever the last combobox selection was
(as if I had clicked on it in the combobox list).
I have no idea what would be causing this, but I managed to stop it by adding:
DropDownBox1.Value = Null to the end of my code which caused the combobox default to be blank.
However this also stopped it from searching while I typed in the box, narrowing down my search with each keystroke.
Which was a desired feature of the drop down list.
Is there another way to stop the original problem so I don't have to use the DropDownBox1.Value = Null solution?
Any suggestions will be greatly appreciated.
Here is the code for my Combobox.
Private Sub DropDownBox1_Change()
Dim findWhat, sRng As Range, lRw As Long, fRng As Range
findWhat = Range("AC1").Value
lRw = Range("A" & Rows.Count).End(xlUp).Row
Set sRng = Range("A2", "A" & lRw)
On Error Resume Next
Set fRng = sRng.Find(findWhat, Range("A" & lRw), xlValues, xlWhole, xlByRows, xlNext)
On Error GoTo 0
If Not fRng Is Nothing Then
Application.Goto fRng
ActiveWindow.ScrollRow = fRng.Row
End If
DropDownBox1.Value = Null
End Sub
The job numbers are located in Column A on the sheet.
The combobox worked fine until I discovered that when I was no longer working with the combobox and I was entering something in column A
and tried to tab over to the next column, the sheet scrolled to the cell of whatever the last combobox selection was
(as if I had clicked on it in the combobox list).
I have no idea what would be causing this, but I managed to stop it by adding:
DropDownBox1.Value = Null to the end of my code which caused the combobox default to be blank.
However this also stopped it from searching while I typed in the box, narrowing down my search with each keystroke.
Which was a desired feature of the drop down list.
Is there another way to stop the original problem so I don't have to use the DropDownBox1.Value = Null solution?
Any suggestions will be greatly appreciated.
Here is the code for my Combobox.
Private Sub DropDownBox1_Change()
Dim findWhat, sRng As Range, lRw As Long, fRng As Range
findWhat = Range("AC1").Value
lRw = Range("A" & Rows.Count).End(xlUp).Row
Set sRng = Range("A2", "A" & lRw)
On Error Resume Next
Set fRng = sRng.Find(findWhat, Range("A" & lRw), xlValues, xlWhole, xlByRows, xlNext)
On Error GoTo 0
If Not fRng Is Nothing Then
Application.Goto fRng
ActiveWindow.ScrollRow = fRng.Row
End If
DropDownBox1.Value = Null
End Sub