| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
where do I put the code?!
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
where do I put the code?!
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: 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
where do I put the code?!
Do I put the code into 'this workbook'?
What would I put onto each sheet to make it work? "Stefi" wrote: 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: 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
where do I put the code?!
Hello
If you are calling sheet events, consider using at workbook level in Thisworkbook. HTH Cordially Pascal "Min" a écrit dans le message de news: ... 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
where do I put the code?!
Do i place the code in 'This workbook'? How do I get each sheet to refer to it?
Apologies, I'm obviously an amateur! "papou" wrote: Hello If you are calling sheet events, consider using at workbook level in Thisworkbook. HTH Cordially Pascal "Min" a écrit dans le message de news: ... 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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
where do I put the code?!
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: Do I put the code into 'this workbook'? What would I put onto each sheet to make it work? "Stefi" wrote: 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: 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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
where do I put the code?!
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 "papou" wrote: Hello Show at least a portion of the code you have on one sheet. Cordially Pascal "Min" a écrit dans le message de news: ... Do i place the code in 'This workbook'? How do I get each sheet to refer to it? Apologies, I'm obviously an amateur! "papou" wrote: Hello If you are calling sheet events, consider using at workbook level in Thisworkbook. HTH Cordially Pascal "Min" a écrit dans le message de news: ... 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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
where do I put the code?!
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 "Min" a écrit dans le message de news: ... 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 "papou" wrote: Hello Show at least a portion of the code you have on one sheet. Cordially Pascal "Min" a écrit dans le message de news: ... Do i place the code in 'This workbook'? How do I get each sheet to refer to it? Apologies, I'm obviously an amateur! "papou" wrote: Hello If you are calling sheet events, consider using at workbook level in Thisworkbook. HTH Cordially Pascal "Min" a écrit dans le message de news: ... 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? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
where do I put the code?!
You are a star!!
Thank you so much for being so patient and for all your help. I'm very grateful. Kind regards Min "papou" wrote: 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 "Min" a écrit dans le message de news: ... 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 "papou" wrote: Hello Show at least a portion of the code you have on one sheet. Cordially Pascal "Min" a écrit dans le message de news: ... Do i place the code in 'This workbook'? How do I get each sheet to refer to it? Apologies, I'm obviously an amateur! "papou" wrote: Hello If you are calling sheet events, consider using at workbook level in Thisworkbook. HTH Cordially Pascal "Min" a écrit dans le message de news: ... 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? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
where do I put the code?!
Thanks for your feedback!
As for my beeing a star, this might be just a tiny bit too far fetched ;-) Cordially Pascal "Min" a écrit dans le message de news: ... You are a star!! Thank you so much for being so patient and for all your help. I'm very grateful. Kind regards Min "papou" wrote: 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 "Min" a écrit dans le message de news: ... 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 "papou" wrote: Hello Show at least a portion of the code you have on one sheet. Cordially Pascal "Min" a écrit dans le message de news: ... Do i place the code in 'This workbook'? How do I get each sheet to refer to it? Apologies, I'm obviously an amateur! "papou" wrote: Hello If you are calling sheet events, consider using at workbook level in Thisworkbook. HTH Cordially Pascal "Min" a écrit dans le message de news: ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
| Code expantion , with code! | Excel Discussion (Misc queries) | |||
| Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
| copying vba code to a standard code module | Excel Discussion (Misc queries) | |||
| Write a code by code | Excel Discussion (Misc queries) | |||
Linear Mode
