Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for positioning cursor in many sheets
Hello!
I have a few workbooks that contain tab names that are alphabetic (for five of the sheets) and the rest, which can be x amount of sheets, are all numeric. I want the ones with the numeric tabs to each have the cursor position at E4 when the workbook is opened. Is there a way to do this with code? I know I can specify each sheet individually, but I need code that uses a variable, like: If the tab is numeric, position the cursor at E4. Any help will be greatly appreciated! Sandy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for positioning cursor in many sheets
Sandy
one way of doing this: Application.ScreenUpdating = False ' Stop the screen flickering Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")).Select ' Group the sheets Range("E4").Select ' Select the start position for the sheets Sheets("Sheet1").Select ' Remove the grouping by selecting another sheet Application.ScreenUpdating = True Just switch on the macro recorder and do what you want to do manually. Click on the first tab to be selected, shift and click on the last sheet to be selected, select the cell you want to position at. Select the sheet where you want to be when you've finished and switch off the recorder. You should get something similar to the above (without the screen updating bit) Regards Trevor "Sandy" wrote in message ... Hello! I have a few workbooks that contain tab names that are alphabetic (for five of the sheets) and the rest, which can be x amount of sheets, are all numeric. I want the ones with the numeric tabs to each have the cursor position at E4 when the workbook is opened. Is there a way to do this with code? I know I can specify each sheet individually, but I need code that uses a variable, like: If the tab is numeric, position the cursor at E4. Any help will be greatly appreciated! Sandy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for positioning cursor in many sheets
Right click on the excel icon to the left of FILEview codeinsert thissave workbook. I tested with a worksheet tab where 8 was the name Private Sub Workbook_SheetActivate(ByVal Sh As Object) If IsNumeric(ActiveSheet.Name) Then [e4].Activate End Sub "Sandy" wrote in message ... Hello! I have a few workbooks that contain tab names that are alphabetic (for five of the sheets) and the rest, which can be x amount of sheets, are all numeric. I want the ones with the numeric tabs to each have the cursor position at E4 when the workbook is opened. Is there a way to do this with code? I know I can specify each sheet individually, but I need code that uses a variable, like: If the tab is numeric, position the cursor at E4. Any help will be greatly appreciated! Sandy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for positioning cursor in many sheets
Using the worksheet Activate event will position the cursor each time the
sheet is activated - this could be a major source of irritation to the user, unless that it is your intent: that they be repositioned at E4 each time they enter the sheet. - If you only want this to occur when the workbook is opened put code in the workbook_open event Private Sub Workbook_Open() Dim sh As Worksheet Dim aSh As Worksheet Set aSh = ActiveSheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets If IsNumeric(sh.Name) Then Application.Goto _ Reference:=sh.Range("A1"), _ Scroll:=True sh.Range("E4").Select End If Next Application.ScreenUpdating = True aSh.Select End Sub This puts A1 as the upper left corner with E4 selected. If you want E4 in the upper left corner and selected, then change A1 to E4 and delete the line sh.Range("E4").Select Note that using notation like [E4] is three times as slow as using Range("E4") -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
move cursor on one sheet moves cursor on all sheets | Excel Discussion (Misc queries) | |||
Cursor positioning | Excel Discussion (Misc queries) | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
Moving cursor to identical cells across multiple sheets | Excel Discussion (Misc queries) | |||
IDE - 2003 - Cursor in Code window | Excel Discussion (Misc queries) |