Tab sheets

  • Thread starter Thread starter Denny Leung
  • Start date Start date
D

Denny Leung

Dear all,

I've two questions:

1. I've a file with over forty worksheets. Every time when
I want to go to my desired worksheet, I need to scroll
through the tabs with arrow buttons (because some
worksheets are located behind the visible screen). Is
there any way I can go to my desired worksheet by one
click or single action?

2. I've a worksheet and Column A is for the inputting date
(i.e. the date for inputting a record). Is there any way
the date can be input automatically when I enter the data
into other fields of that row? If yes, how?

Thanks in advance.
 
Hi

Right-click on sheets ruler (arrows in left lower corner). Select the sheet
from list.


Arvi Laanemets
 
Hi Denny!

It's best to keep to one question per post.

Question 1

Try right clicking those little arrows to the left of the worksheet
tabs. It brings up a clickable list of sheets.

Question 2

Here's the base subroutine that needs adaptation depending upon where
you want the date stamp and which cells you want it to be triggered
by. At present it is set to put in the system date 1 cell to the left
of the active cell each time there is a change in column B.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo errHandler
If Target.Column = 2 Then
Application.EnableEvents = False
Target.Offset(0, -1).Value = Date
End If
errHandler:
Application.EnableEvents = True
End Sub

This code goes into the sheet module and that can be achieved by:

Selecting the code below
Selecting the sheet in Excel that you want it in
Right click the sheet tab for that sheet
Paste in the large window in the Visual Basic Editor.
Back into Excel and test


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 25th July: Barundi (Anniv of 7th
Republic), Cook Islands (Rarotonga: Gospel Day), Costa Rica
(Guanacaste Day), Cuba (National Revolution), Ecuador (Foundation of
Guyaquil Day), Faroe Islands (Vestanstevna), Haiti (Papa Ogou),
Puerto Rico (Constitution Day), Spain (Galicia National Day, Santiago
Day, St. James Day), Tibet (Yalong Cultural Festival), Tunisia
(Republic Day). Observances: Voudon (Papa Ogou St Jacques le Majeur).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Denny,
Additional information for #1 list of sheets
you might want to sort your worksheet tabs so that right-clicking
on sheet tab navigation keys shows an alphabetized list
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets
and to invoke the "More Sheets" a bit quicker
http://groups.google.com/[email protected]

Additional information for #2 automatic date entry
More on Event macros
http://www.mvps.org/dmcritchie/excel/event.htm



Norman Harker said:
[clipped, answered both questions]
Click to expand...
 
I have the Worksheet you need. Request it and I'll send it to you..
(e-mail address removed)
 
Back
Top