Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
regular code module
I was told to put something in a code module and something into a regular
code module. My problem is I know where to put it into a code module but where do I find a regular code module? This is what I have: CODE MODULE:Private Sub Workbook_SheetSelectionChange( _ ByVal Sh As Object, ByVal Target As Range) Application.EnableEvents = False SynchSheets Application.EnableEvents = True End Sub REGULAR CODE MODULE:Sub SynchSheets() ' Duplicates the active sheet's active cell upperleft cell ' Across all worksheets If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub Dim UserSheet As Worksheet, sht As Worksheet Dim TopRow As Long, LeftCol As Integer Dim UserSel As String Application.ScreenUpdating = False ' Remember the current sheet Set UserSheet = ActiveSheet ' Store info from the active sheet TopRow = ActiveWindow.ScrollRow LeftCol = ActiveWindow.ScrollColumn UserSel = ActiveWindow.RangeSelection.Address ' Loop through the worksheets For Each sht In ActiveWorkbook.Worksheets If sht.Visible Then 'skip hidden sheets sht.Activate Range(UserSel).Select ActiveWindow.ScrollRow = TopRow ActiveWindow.ScrollColumn = LeftCol End If Next sht ' Restore the original position UserSheet.Activate Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
regular code module
The first bit goes in the Thisworkbook code module.
'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code The second bit goes in what I think you mean by a code module, one created by InsertModule in the VBIDE. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "billy boy" wrote in message ... I was told to put something in a code module and something into a regular code module. My problem is I know where to put it into a code module but where do I find a regular code module? This is what I have: CODE MODULE:Private Sub Workbook_SheetSelectionChange( _ ByVal Sh As Object, ByVal Target As Range) Application.EnableEvents = False SynchSheets Application.EnableEvents = True End Sub REGULAR CODE MODULE:Sub SynchSheets() ' Duplicates the active sheet's active cell upperleft cell ' Across all worksheets If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub Dim UserSheet As Worksheet, sht As Worksheet Dim TopRow As Long, LeftCol As Integer Dim UserSel As String Application.ScreenUpdating = False ' Remember the current sheet Set UserSheet = ActiveSheet ' Store info from the active sheet TopRow = ActiveWindow.ScrollRow LeftCol = ActiveWindow.ScrollColumn UserSel = ActiveWindow.RangeSelection.Address ' Loop through the worksheets For Each sht In ActiveWorkbook.Worksheets If sht.Visible Then 'skip hidden sheets sht.Activate Range(UserSel).Select ActiveWindow.ScrollRow = TopRow ActiveWindow.ScrollColumn = LeftCol End If Next sht ' Restore the original position UserSheet.Activate Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
regular code module
In the VBA development environment (Alt+F11), at the left you see the
Project Explorer. The first macro is an event macro to detect workbook-level events. It should be pasted to the module that opens when you double-click the ThisWorkbook object. For a regular code module, use Insert|Module. It will create a "regular" code module called Module1 and open it. Paste the code in there. HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
regular code module
Insert, Module in the VBE creates a new 'regular's module. Your "code
module" code goes in the ThisWorkbook module. Semantically I think the term "code module" can refer to all workbook, worksheet, userform and standard modules. -- Jim "billy boy" wrote in message ... |I was told to put something in a code module and something into a regular | code module. My problem is I know where to put it into a code module but | where do I find a regular code module? | This is what I have: | | CODE MODULE:Private Sub Workbook_SheetSelectionChange( _ | ByVal Sh As Object, ByVal Target As Range) | Application.EnableEvents = False | SynchSheets | Application.EnableEvents = True | End Sub | | REGULAR CODE MODULE:Sub SynchSheets() | ' Duplicates the active sheet's active cell upperleft cell | ' Across all worksheets | If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub | Dim UserSheet As Worksheet, sht As Worksheet | Dim TopRow As Long, LeftCol As Integer | Dim UserSel As String | | Application.ScreenUpdating = False | | ' Remember the current sheet | Set UserSheet = ActiveSheet | | ' Store info from the active sheet | TopRow = ActiveWindow.ScrollRow | LeftCol = ActiveWindow.ScrollColumn | UserSel = ActiveWindow.RangeSelection.Address | | ' Loop through the worksheets | For Each sht In ActiveWorkbook.Worksheets | If sht.Visible Then 'skip hidden sheets | sht.Activate | Range(UserSel).Select | ActiveWindow.ScrollRow = TopRow | ActiveWindow.ScrollColumn = LeftCol | End If | Next sht | | ' Restore the original position | UserSheet.Activate | Application.ScreenUpdating = True | End Sub | | | | |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
regular code module
billy
The Code Module code goes into the Thisworkbook module. For Regular code select InsertModule Gord Dibben Excel MVP On Tue, 13 Dec 2005 10:53:01 -0800, billy boy wrote: I was told to put something in a code module and something into a regular code module. My problem is I know where to put it into a code module but where do I find a regular code module? This is what I have: CODE MODULE:Private Sub Workbook_SheetSelectionChange( _ ByVal Sh As Object, ByVal Target As Range) Application.EnableEvents = False SynchSheets Application.EnableEvents = True End Sub REGULAR CODE MODULE:Sub SynchSheets() ' Duplicates the active sheet's active cell upperleft cell ' Across all worksheets If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub Dim UserSheet As Worksheet, sht As Worksheet Dim TopRow As Long, LeftCol As Integer Dim UserSel As String Application.ScreenUpdating = False ' Remember the current sheet Set UserSheet = ActiveSheet ' Store info from the active sheet TopRow = ActiveWindow.ScrollRow LeftCol = ActiveWindow.ScrollColumn UserSel = ActiveWindow.RangeSelection.Address ' Loop through the worksheets For Each sht In ActiveWorkbook.Worksheets If sht.Visible Then 'skip hidden sheets sht.Activate Range(UserSel).Select ActiveWindow.ScrollRow = TopRow ActiveWindow.ScrollColumn = LeftCol End If Next sht ' Restore the original position UserSheet.Activate Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) | |||
Change case...help please | Excel Worksheet Functions | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions |