Getting run time error on code not called???

[wildwally]

I run any bit of code and get a run time error on something that works fine when its called on its own.

Sub updatecontrolnum()


Dim controller As String
Dim rng1 As Range
Dim setg As Worksheet
Dim opit As Worksheet
Dim user As String

Set setg = Sheets("Settings")
Set opit = Sheets("Options")
'On Error Resume Next
user = Sheets("Settings").USERNAME.Value

With opit.Range("LEMPNAME")
            [highlight]Set rng1 = .Find(What:=user, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)[/highlight]
            If Not rng1 Is Nothing Then
                controller = rng1.Offset(0, 2).Value
            End If
End With

Sheets("Settings").CNTRLNUM.Value = controller & Range("CONTNUM").Value
            
End Sub

Here is the code i get the error with. this is located in a module and the code running has nothing to do with this at all. Any suggestions?

 

[Ruddles]

I was under the impression that a run-time error could only be triggered on a piece of code that was actually being executed.

Place a breakpoint against the first executable statement in updatecontrolnum then run the code you believe has nothing to do with it, and make 100% sure it really isn't being called without your knowledge.

 

[wildwally]

Ok inserted a break point and the code stopped at the break point highlighting the line that the break point was set at. Would this mean i'm triggering this somewhere?

Didn't recieve any run time - but it hasn't finished running its in debug mode

 

[Ruddles]

If it stopped at the breakpoint and highlighted the statement, that means you triggered that code to run. By the sounds of it you weren't expecting that to happen so I guess your next step is to work out why that's happening.

(Incidentally, if you hit F5 to resume running or step forward through the code one statement at a time using the F8 key, you'll probably get the run-time error at the same statement as you did before.)

That procedure must be being called explicitly - that is, it's not being triggered by an event like the workbook opening or a worksheet changing - so look through all your code for anywhere where that name appears and set a breakpoint against each occurrence. The next time that subroutine is called when you're not expecting it to be, you'll know where it's being called from and you'll be able to examine the contents of your program variables at that point (in case that's the problem).

(Note that breakpoints aren't saved so when you close the workbook, the breakpoints are removed.)

 

[RoryA]

Looking at the code I'd guess you have a control bound to a sheet?

 

[wildwally]

Ok, here's what i found and it still doesn't make since to me.

I do have a key cells used as buttons that trigger userforms to show. But i commented each line of code out to find out where things are going wrong. So I narrowed it down to the enter button on one of my userforms. Below is the code and the highlighted text is where it jumps to the code provided in the first post. Still not understanding why it jumps.

Private Sub enter_Click()
'Add new record to the account name
Dim ws As Worksheet
'Inserts row into list
Set ws = Sheets("Options")


ws.Select
ws.Range("A9:B9").Select
[highlight]Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove[/highlight]
'Add user entries
       
ws.Range("A9").Value = NEWACCTNAME.ACCTTYPECB.Value
ws.Range("B9").Value = NEWACCTNAME.ACCTNAMETB.Value
       
'Sort the fields by the Account Name column.
ActiveWorkbook.Worksheets("Options").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Options").Sort.SortFields.Add Key:=Range("LACCTNAME") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Options").Sort
    .SetRange Range("ALLACCTNM")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

'Clean form
NEWACCTNAME.ACCTTYPECB.Value = ""
NEWACCTNAME.ACCTNAMETB.Value = ""

End Sub

it jumps to this which is located on another worksheet. This code is associated with an activex combobox located on the "settings" sheet.

Private Sub USERNAMECB_change()
FormCommandscontinued.updatecontrolnum
End Sub

This code then jumps to the module and code shown in first post.


what have i done wrong?

 

[RoryA]

Is the combobox linked to the sheet using the LinkedCell and/or ListFillRange properties? That would do it.

 

[wildwally]

Yes, it is linked to another part of that sheet. So what i'm understanding is the source sheet has changed so the form is making sure the list hasn't changed which is triggering the update command. Makes sense when you think about it in a broader view.

Next question is there a way to only trigger the combobox to update after user update? another words replace the change command? I can think of a couple other ways to do this but it would a manual process required by user - and i need it automated. I could also change the source olcation to another sheet but I perfer not to go this direction if not required.

 

[RoryA]

Either don't bind the control to the sheet (use the List property to populate it from the range in the sheet's activate event for example) or have a public boolean variable that you set when you are going to make changes to the sheet and you don't want the controls to trigger (then set it back to False afterwards):

Private Sub USERNAMECB_change()
If blnSkipEvents then exit sub
FormCommandscontinued.updatecontrolnum
End Sub

 

[wildwally]

I tried the lostfocus - but not instant enough for me; i'll try the public boolean variable.


Thanks for the help.