Stephen Byrom
asked on
MS Excel 2010/2013
Hi,
I have an active X combo box on a worksheet that moves the cursor to the correct cell when the user selects an item from the list.
The problem I have is that whenever a cell is changed in any other worksheet, the code behind the combo box runs and the user is taken to the sheet with the combo box on it, even though nothing has changed in the combo box! I have tried adding "thisworkbook.sheets" etc but to no avail.
this is the simple code behind the active X combo box;
Private Sub cboCust_Change()
Dim c As Range
For Each c In Range("A4:A500")
If c.Value = cboCust.Value Then
Application.Goto c, Scroll:=True
c.select
Exit Sub
End If
Next
End Sub
Any help is appreciated.
Thanks
I have an active X combo box on a worksheet that moves the cursor to the correct cell when the user selects an item from the list.
The problem I have is that whenever a cell is changed in any other worksheet, the code behind the combo box runs and the user is taken to the sheet with the combo box on it, even though nothing has changed in the combo box! I have tried adding "thisworkbook.sheets" etc but to no avail.
this is the simple code behind the active X combo box;
Private Sub cboCust_Change()
Dim c As Range
For Each c In Range("A4:A500")
If c.Value = cboCust.Value Then
Application.Goto c, Scroll:=True
c.select
Exit Sub
End If
Next
End Sub
Any help is appreciated.
Thanks
ASKER
Thanks GP but I tried that earlier, although with active sheet...
Dim c As Range
If ActiveSheet = Sheets("NonAuto") Then
For Each c In Range("A4:A500")
If c.Value = cboCust.Value Then
Application.Goto c, Scroll:=True
c.Select
Exit Sub
End If
Next
End If
but that didn't work either.
I have added a snapshot of the workbook to make it easier to see what the problem is
Call-Offs.xlsm
Dim c As Range
If ActiveSheet = Sheets("NonAuto") Then
For Each c In Range("A4:A500")
If c.Value = cboCust.Value Then
Application.Goto c, Scroll:=True
c.Select
Exit Sub
End If
Next
End If
but that didn't work either.
I have added a snapshot of the workbook to make it easier to see what the problem is
Call-Offs.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it.
Seems a bit clumsy but this works..
Private Sub cboCust_Change()
If ActiveSheet.Name = "NonAuto" Then
Dim c As Range
For Each c In Range("A4:A500")
If c.Value = cboCust.Value Then
Application.Goto c, Scroll:=True
c.Select
Exit Sub
End If
Next
End If
End Sub
Seems a bit clumsy but this works..
Private Sub cboCust_Change()
If ActiveSheet.Name = "NonAuto" Then
Dim c As Range
For Each c In Range("A4:A500")
If c.Value = cboCust.Value Then
Application.Goto c, Scroll:=True
c.Select
Exit Sub
End If
Next
End If
End Sub
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for olorin57's comment #a39773497
for the following reason:
trial and error for about two hours
Accepted answer: 0 points for olorin57's comment #a39773497
for the following reason:
trial and error for about two hours
ASKER
wow,
I think we posted at the same time.
In fact if I look at the posting times I was a minute later than MacroShadow, so if a moderator wants to award the points to him/her, then I don't mind at all.
I think we posted at the same time.
In fact if I look at the posting times I was a minute later than MacroShadow, so if a moderator wants to award the points to him/her, then I don't mind at all.
ASKER
Thanks for your time
something like:
Open in new window