Weird Behaviour of Code

[Carlo]

Hi everybody

by changing the printarea of a sheet with VBA, my code jumps to the onchange
code of a Combobox, which has nothing to do with the printarea.

To be more precisely: after initializing the form, the changing of the
printarea
works fine, after using the combobox, the changing of the printarea triggers
the
onchange event of the combobox. It's always the same combobox!
In the code of the combobox i change the printarea too, but that shouldn't
be the
problem, because it shouldn't get activated at all!

Anyone had some similar issues?

Thanks for any help

Carlo

 

[Sandy]

Try setting Application.EnableEvents = False, then setting it to true
when you are finished. If this doesn't work post the code for the combo
box change event, and the code for the changing of the print area


HTH

 

[Carlo]

Hi Sandy and all the others

Thanks for your answer, unfortunately it didn't work!

here's my code, hope it isn't to messy
------------------------------------------------------------------------------------------------
Private Sub Cmb_Template_Change()

Dim TemplateRow As Integer
Dim TemplateCol As Integer
Dim ArrayRoman(5) As String

TemplateRow = 11 + Me.Cmb_Template.ListIndex * 10
TemplateCol = 9

ArrayRoman(0) = "I"
ArrayRoman(1) = "II"
ArrayRoman(2) = "III"
ArrayRoman(3) = "IV"
ArrayRoman(4) = "V"
ArrayRoman(5) = "VI"

If ActiveSheet.Cells(TemplateRow + 1, TemplateCol) = 1 Or
ActiveSheet.Cells(TemplateRow + 1, TemplateCol) = "" Then
Me.Opt_1Page = True 'here it jumps to the Opt_1Page_Click() which is ok
Else
Me.Opt_2Page = True 'here it jumps to the Opt_2Page_Click() which is ok
End If

Me.Cmb_Title = ActiveSheet.Cells(TemplateRow, TemplateCol + 1)
Me.Cmb_Logo = ActiveSheet.Cells(TemplateRow + 1, TemplateCol + 1)

For i = 0 To 5
For j = 1 To 8
Me("Cmb_" & ArrayRoman(i) & "_" & j) = ActiveSheet.Cells(TemplateRow
+ j - 1, TemplateCol + 2 + i)
Next j
Next i

Me.Cmb_Level_Row2 = ActiveSheet.Cells(TemplateRow + 8, TemplateCol + 3)
Me.Cmb_Level_Row3 = ActiveSheet.Cells(TemplateRow + 8, TemplateCol + 4)
Me.Cmb_Level_Row5 = ActiveSheet.Cells(TemplateRow + 8, TemplateCol + 6)
Me.Cmb_Level_Row6 = ActiveSheet.Cells(TemplateRow + 8, TemplateCol + 7)

End Sub

Private Sub Opt_1Page_Click()
Call SetPages
End Sub

Private Sub Opt_2Page_Click()
Call SetPages
End Sub

Sub SetPages()
'Application.EnableEvents = False
If Opt_1Page = True Then
Sheets("Final").PageSetup.PrintArea = "$A$1:$P$61" 'after setting
the printarea, the debugger jumps up to the cmb_template_Change, but only if
you changed the combo at least once!!!!
Call secondpage(False)
Else
Sheets("Final").PageSetup.PrintArea = "$A$1:$P$122"
Call secondpage(True)
End If
'Application.EnableEvents = True
End Sub

Private Sub UserForm_Initialize()
Sheets("Layout").Select
Call SetPages
End Su

 

[Bob Phillips]

Is there something in the secondpage procedure that can trigger it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Carlo]

Appendix:

if i delete these lines:
---------------------------------------------------------------------------
If ActiveSheet.Cells(TemplateRow + 1, TemplateCol) = 1 Or
ActiveSheet.Cells(TemplateRow + 1, TemplateCol) = "" Then
Me.Opt_1Page = True
Else
Me.Opt_2Page = True
End If

 

[Carlo]

Hi Bob

no, after i change the combobox, it looks for the selected entry the user
made and
looks if a cell is equal (1 or "") or else....after that it changes the
optbuttons from
where it goes to SetPages, where it jumps back to the Cmb_Template_Change
event, after changing the Printarea!

 

[Sandy]

Here's my take on what might be going on. When your if statement
changes the OT_1Page or then Opt_2Page button something in your
combobox is changing. Is there any references to these 2 buttons in the
combo box at all? Take a good look at the code, there must be a
reference to one of these buttons (or both), especially if you removed
the if statement and the code worked. If you can, post the combobox
code, someone's fresh eyes, and head may see something that you might
have missed... as we all know when we've starred at something for a
little too long.


HTH

Sandy

 

[Carlo]

Hi Sandy

Well the weirdest thing is, that the code jumps to the combobox after setting
the printarea!! And while changing the printarea nothing should change on the
combobox, because it refers to a whole diffrent sheet, and not to the sheet
on which I change the printarea.

I don't have a Combobox code, the only thing i have is the onchange code and
this one i have already posted
I could send you the file, where you could look at the whole code, if that
would
help!

Yeah you're right, after searching for errors in the whole code your eyes get
lazy and tend to overlook a lot of things!

Thanks for your kind help

Carlo

 

[Carlo]

Now i found something else

It also jumps to the onchange effect if i write anything on the layout-sheet!!
I have a "Save" Button which also gets this effect!!! I think i try to redo
the
combobox......perhaps it helps!

If anyone has another suggestion please let me know!

Carlo

 

[Carlo]

Ok, i've changed some things, and now it looks like it works.
Not really a clean solution, more a quick and dirty try, but
i don't care.

I erased the onchange event, made a button which has the
same code like the onchange had, and it works.

Thanks for all that helped, i kindly appreciated it.

Carlo

 

[Chip Pearson]

Carlo,

When you get bizarre code execution errors, such as code execution jumping
into unrelated procedures, it often is due to messiness in VBA's internal
code storage mechanism. When you do a lot of editing of a VBA project, VBA
doesn't always fully and completely delete its own version of the code you
edit. (This is not something you are doing wrong -- it is all internal to
VBA itself.) The preferred solution is to export all your VBA code to text
files, Remove the modules from the project, and then re-import the saved
text files. This causes VBA to purge its internal storage of the code and
start with a clean slate. Rob Bovey has written a fine and free add-in to do
this automatically. Go to
http://www.appspro.com/Utilities/CodeCleaner.htm and download CodeCleaner
4.4. It will add an item to your Tools menu named "Clean Project". Note that
the VBA project must be unlocked. Also, see the note on that web page
regarding protection in Excel 2002 and 2003.

Cleaning the VBA Project can clear up lots of bizarre behavior in your code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Carlo]

hey Chip

thanks a lot for the help.
Will have a look at the tool.
Will surely come in handy!

yours

Carlo