Solved

MS Excel 2010/2013

Posted on 2014-01-11
7
244 Views
Last Modified: 2014-01-11
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
0
Comment
Question by:Stephen Byrom
  • 5
7 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 39773295
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
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39773301
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
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39773496
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39773497
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
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39773503
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
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39773504
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
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 39773507
Thanks for your time
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now