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
LVL 1
Stephen ByromWarehouse/ShippingAsked:
Who is Participating?
 
MacroShadowConnect With a Mentor Commented:
You were missing the .name

Private Sub cboCust_Change()
    Dim c As Range
    If ActiveSheet.Name = Sheets("NonAuto").Name 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
End Sub

Open in new window

0
 
gplanaCommented:
I think you can avoid this problem just by adding an if to execute the code only if the currentcell is the cell you want.

something like:

Private Sub cboCust_Change()
Dim c As Range
   if activeCell.row=3 and activeCell.column=1 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
End Sub

Open in new window

0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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.
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks for your time
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.