where do I put the code?!

M

Min

I have a workbook with 18 sheets in it, and each sheet has exactly the same
vb code. When I need to change something I'm having to change it on each
sheet. Is there somewhere that I can put just one lot of code and all the
sheets will work from it?
 
S

Stefi

Show the code! Probably you'll have to make some modofication on it before
it'll capable of handling different sheets.

Regards,
Stefi


„Min†ezt írta:
 
M

Min

Do I put the code into 'this workbook'?
What would I put onto each sheet to make it work?
 
P

papou

Hello
If you are calling sheet events, consider using at workbook level in
Thisworkbook.

HTH
Cordially
Pascal
 
M

Min

Do i place the code in 'This workbook'? How do I get each sheet to refer to it?
Apologies, I'm obviously an amateur!
 
P

papou

Hello
Show at least a portion of the code you have on one sheet.

Cordially
Pascal
 
S

Stefi

I can't answer if I don't see the code! I don't know even if it's an event
code or not!
Stefi


„Min†ezt írta:
 
M

Min

This is some of the code.
I'm sorry but I really am an amateur so any help is greatly appreciated!
Many thanks


Private Sub CheckBox1_Click()
If CheckBox1 = False Then
Columns("K:U").Select
Selection.EntireColumn.Hidden = True
Range("A2").Select
OptionButton1 = False 'Show all
ActiveWindow.ScrollColumn = 1

Else
Columns("K:U").Select
Selection.EntireColumn.Hidden = False
ActiveWindow.ScrollColumn = 11
Range("K2").Select
End If

End Sub

If Not Intersect(Target, Range("R2:R36")) Is Nothing Then
Select Case Target
Case "2a" To "5c"
icolor = 33
Case "1c"
icolor = 45
Case "1b"
icolor = 4
Case "1a"
icolor = 33
Case "w"
icolor = 3
Case Else
End Select
Target.Interior.ColorIndex = icolor
End If
 
P

papou

Min
As regards Chekboxes you can't use code from Thisworkbook (I think you could
use a class module).

Where does the second part of you code belong to?
I mean the part starting with:
"If Not Intersect(Target, Range("R2:R36")) Is Nothing Then"

If you are firing the change event from the sheet use:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

If you are firing the selection change use:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

End Sub

The workbook event are available if you double-click on Thisworkbook object
in the Project window.

HTH
Cordially
Pascal
 
M

Min

You are a star!!
Thank you so much for being so patient and for all your help.
I'm very grateful.
Kind regards
Min
 
P

papou

Thanks for your feedback!

As for my beeing a star, this might be just a tiny bit too far fetched ;-)

Cordially
Pascal
 
Top