Auto Skipping and Protecting Worksheet

I have a workbook that contains 14 sheets. I have a sheet for each month 
followed by 2 sheets for information. 

Each Month sheet has the following column headings associated from columns A 
through J:- 

Owner; from date; number of days; to date, address, ID, month, input by; 
date; time. 

I have to input data in columns A, B, C E, H, I and J. 

Columns A and H are pick lists. 

The following VBA is present to allow automatic population of columns I and J.
Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    On Error GoTo ws_exit
    With Target
        If .Column = 8 Then
            With .Offset(0, 1)
                .Value = Date
                .NumberFormat = "dd mmm yy"
            End With
            With .Offset(0, 2)
                .Value = Now
                .NumberFormat = "hh:mm AM/PM"
            End With
        End If
    End With

ws_exit:
    Application.EnableEvents = True

End Sub

To allow tabbing to the next cell I have locked cells that don't require 
input and then protected the sheet (i.e. Columns D, F, G, I and J).

The problem I have is that once an item is picked up from the drop down to 
populate column H, then only the date is populated.

If I unprotect the sheet and select an item from the drop down list in 
column H then both the date and time are populated.

Is there any way that I can have columns I and J un-locked (to allow date 
and time to be populated by the VBA) and when I tab from column H it 
automatically takes me to the next row and in column A?

Any help would be most appreciated.
0
PankMehta (14)
1/21/2005 9:11:04 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
362 Views

Similar Articles

[PageSpeed] 0

Private Sub Worksheet_Change(ByVal Target As Range)
    me.protect UserInterfaceOnly:=True
    Application.EnableEvents = False
    On Error GoTo ws_exit
    With Target
        If .Column = 8 Then
            With .Offset(0, 1)
                .Value = Date
                .NumberFormat = "dd mmm yy"
            End With
            With .Offset(0, 2)
                .Value = Now
                .NumberFormat = "hh:mm AM/PM"
            End With
        End If
    End With

ws_exit:
    Application.EnableEvents = True

End Sub

If the sheet protection has a password and you are using xl2002 or later,
then you would change to

 me.protect Password:="ABCD", UserInterfaceOnly:=True

replace ABCD with your password.

-- 
Regards,
Tom Ogilvy


"Pank Mehta" <PankMehta@discussions.microsoft.com> wrote in message
news:A5283D7E-E7AB-43CE-AE78-91246E18BFAB@microsoft.com...
> I have a workbook that contains 14 sheets. I have a sheet for each month
> followed by 2 sheets for information.
>
> Each Month sheet has the following column headings associated from columns
A
> through J:-
>
> Owner; from date; number of days; to date, address, ID, month, input by;
> date; time.
>
> I have to input data in columns A, B, C E, H, I and J.
>
> Columns A and H are pick lists.
>
> The following VBA is present to allow automatic population of columns I
and J.
> Private Sub Worksheet_Change(ByVal Target As Range)
>
>     Application.EnableEvents = False
>     On Error GoTo ws_exit
>     With Target
>         If .Column = 8 Then
>             With .Offset(0, 1)
>                 .Value = Date
>                 .NumberFormat = "dd mmm yy"
>             End With
>             With .Offset(0, 2)
>                 .Value = Now
>                 .NumberFormat = "hh:mm AM/PM"
>             End With
>         End If
>     End With
>
> ws_exit:
>     Application.EnableEvents = True
>
> End Sub
>
> To allow tabbing to the next cell I have locked cells that don't require
> input and then protected the sheet (i.e. Columns D, F, G, I and J).
>
> The problem I have is that once an item is picked up from the drop down to
> populate column H, then only the date is populated.
>
> If I unprotect the sheet and select an item from the drop down list in
> column H then both the date and time are populated.
>
> Is there any way that I can have columns I and J un-locked (to allow date
> and time to be populated by the VBA) and when I tab from column H it
> automatically takes me to the next row and in column A?
>
> Any help would be most appreciated.


0
twogilvy (1078)
1/21/2005 4:15:48 PM
Tom,

Many thanks for your suggestions. However, the workbook is not password 
protected only certain cells are locked to enable cells to be skipped where 
data entry is not required. 

Any suggestions you may have to remidy my original problem would be most 
appreciated.


"Tom Ogilvy" wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
>     me.protect UserInterfaceOnly:=True
>     Application.EnableEvents = False
>     On Error GoTo ws_exit
>     With Target
>         If .Column = 8 Then
>             With .Offset(0, 1)
>                 .Value = Date
>                 .NumberFormat = "dd mmm yy"
>             End With
>             With .Offset(0, 2)
>                 .Value = Now
>                 .NumberFormat = "hh:mm AM/PM"
>             End With
>         End If
>     End With
> 
> ws_exit:
>     Application.EnableEvents = True
> 
> End Sub
> 
> If the sheet protection has a password and you are using xl2002 or later,
> then you would change to
> 
>  me.protect Password:="ABCD", UserInterfaceOnly:=True
> 
> replace ABCD with your password.
> 
> -- 
> Regards,
> Tom Ogilvy
> 
> 
> "Pank Mehta" <PankMehta@discussions.microsoft.com> wrote in message
> news:A5283D7E-E7AB-43CE-AE78-91246E18BFAB@microsoft.com...
> > I have a workbook that contains 14 sheets. I have a sheet for each month
> > followed by 2 sheets for information.
> >
> > Each Month sheet has the following column headings associated from columns
> A
> > through J:-
> >
> > Owner; from date; number of days; to date, address, ID, month, input by;
> > date; time.
> >
> > I have to input data in columns A, B, C E, H, I and J.
> >
> > Columns A and H are pick lists.
> >
> > The following VBA is present to allow automatic population of columns I
> and J.
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> >     Application.EnableEvents = False
> >     On Error GoTo ws_exit
> >     With Target
> >         If .Column = 8 Then
> >             With .Offset(0, 1)
> >                 .Value = Date
> >                 .NumberFormat = "dd mmm yy"
> >             End With
> >             With .Offset(0, 2)
> >                 .Value = Now
> >                 .NumberFormat = "hh:mm AM/PM"
> >             End With
> >         End If
> >     End With
> >
> > ws_exit:
> >     Application.EnableEvents = True
> >
> > End Sub
> >
> > To allow tabbing to the next cell I have locked cells that don't require
> > input and then protected the sheet (i.e. Columns D, F, G, I and J).
> >
> > The problem I have is that once an item is picked up from the drop down to
> > populate column H, then only the date is populated.
> >
> > If I unprotect the sheet and select an item from the drop down list in
> > column H then both the date and time are populated.
> >
> > Is there any way that I can have columns I and J un-locked (to allow date
> > and time to be populated by the VBA) and when I tab from column H it
> > automatically takes me to the next row and in column A?
> >
> > Any help would be most appreciated.
> 
> 
> 
0
PankMehta (14)
1/24/2005 2:01:05 PM
Reply:

Similar Artilces:

How to protect data in rows from being seperated during sort
I have a huge spreadsheet that needs to be sorted a million times but several people. The problem is that during some of the sorts they don't always grab the whole row and the data then gets scrambled. how can I protect the sheet so that the rows can be moved and sorted and edited but the data within the rows always stays together If your sheet is not too dynamic, and tends to stay the same size, then simply create a named range. Select *all* the rows and columns concerned. Click in the "Name box", and enter a short appropriate name, like "sort" (no quotes), an...

universal formatting of all cells in all worksheets
I have 140 sheets in an excel document. In column AF I want the width to be set to 15.86. Instead of going to every sheet and making the change, is there a way that I can set the width for that column in all of the sheets right click on a sheet tab>select all sheets>set whatever on the sheet you are on>select any One sheet. -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "jeremyrod" <jeremyrodriguez@cmsmechanical.com> wrote in message news:0b4301c351e9$39fe41e0$a501280a@phx.gbl... > I have 140 sheets in an excel document. In column AF I >...

protect file from getting deleted
I have made a file in Excel 97. I want to protect it in such a way that nobody should be able to delete it. Nobody should also be able to save another file on it. Can anyone help please There is no way that you can with 100% certaintly prevent a file from being deleted or overwritten. This is why the first three rules of computing are backup, backup and backup. When you are done working on a file, copy it from your hard drive to what ever else you may have access to. IE,a second hard drive, server drive, floppy, CDR, zip disk, USB Key drive. If you don't have access to a...

Excluding worksheets from printing
Hi, I currently embed an Excel macro in my spreadsheets which will print the entire workbook (40+ sheets) should the user request it: Private Sub CommandButton2_Click() Dim Sheet As Worksheet Dim lAnswer As Long lAnswer = MsgBox("This report contains " & Sheets.Count & " sheets - Do you want to print them all?", vbYesNo, "Print?") If lAnswer = vbNo Then Exit Sub Else Worksheets.Select Application.Dialogs(xlDialogPrint).Show Sheets("Total").Select End If End Sub However, I have now incorporated several workings sheets that a...

cut and paste to different worksheet
Hi > Experts > I am working on excel sheet to make a daily production report. In my > worksheet, there are 300 rows selected for a week and column from A to W. Column F is named as "STATUS". When a particular job finishes, person on the section puts C (C means Completed) in the column F. What I am after is as soon as column F (Status) goes C, then information for that job from column A to column W should get copied automatically to another worksheet 3. consider in one day 10 jobs gets completed onto a section then 10 jobs infomation from column A to column W s...

Single worksheet, multiple pages?
Hello, I have a single excel worksheet, and due to area's of the sheet I do not want to have printed, (the data is to be printed onto pre printed forms), have used the set print area option to exclude those area's. The problem is now I have multiple pages inside a single worksheet. Each page prints seperatly, so I end up with 5 pages with only a small amount of the full worksheet on each. Thanks in advance Peter Hi peter i'm not sure what you're problem is - sounds like you've set it up and got it working like you want - how would you like it to be different? chee...

Graphics: get data from different worksheets
Hey all, I just wanted to ask if it is possible to make a graphic in a worksheet and get the data from other worksheets...I don't know if I explained myself clearly! Thanks in advance! ...

Question About Importing Columns From A Datasheet to A Worksheet
I am asking for any help you can give me regarding the best way to import columns from a datasheet into another worksheet. I have many worksheets to construct but I will need to include only a few select columns from the datasheet on each worksheet. Each worksheet will include a different assortment of columns. It is my goal to only continuously update the datasheet information, so all the changes will carry over to all the worksheets without me having to manually update every sheet. I know how to do this function per cell, but I cant figure out how to do it by column. Please...

Protecting Selected Cells and Functions
I have a worksheet. In Cell B2 is a Data validation box Listing a range of colleagues names( DRop Down Menu). On selection of a name in B2, the contents of the whole worksheet changes. I like to Protect the worksheet for: 1) Hiding the formulaes 2) And most importantly preventing editing of the contents of any other cell (except B2). and yet be permiitted to: 3) Select contents in Cell B2 (Data Validation Box) 4) Select Auto filters in Row 4 I've tried using the the Tools/ Protect worksheet menu, ticking Select Lock Cells, Select Unlock cell, use auto filters. And in in Format/Cells/...

Column Auto Width and Hidden Rows
I have a macro that selects certain columns and auto sets the width. The problem is it is using data in rows that are hidden. How can I make it ignore these rows when selecting the width? Thanks, KWCounter I'm not sure you can. But you could copy that column (visible cells only) to a new sheet (in column A), autofit that column, keep track of the width and use it on the original column. Option Explicit Sub testme() Dim curWks As Worksheet Dim tempWks As Worksheet Dim rng As Range Dim myWidth As Double Set curWks = ActiveSheet '??? Set tempWks = Workshee...

Enter same text in mulitple worksheets at the same time
I have a workbook that has 109 worksheets in it, now I need to have the same layout on each page is there a way to type it on wrksht 1 so that it will automactally enter the same text through out the rest of the wrkshts? Or is the old fashion way the only way out of this? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200701/1 Select the first sheet (click sheet tab), hold down shift and click the last sheet tab to group the sheets, now enter the text, when done right click any sheet tab and select ungroup sheets Regards, Peo Sjoblom Newbee via ...

Is there an "Execute" worksheet function?
Is there a way I can get Excel to "execute" an expression that is stored in a cell as a test string? Say C4 contains "3+4". Can I put some expression in C5 that will execute that expression and result in "7"? I tried =calculate(C4) and a few other things, but no joy. in the absence of putting = in front of c4, try this =LEFT(C4,FIND("+",C4)-1)+RIGHT(C4,LEN(C4)-FIND("+",C4)) -- Don Guillett Excel MVP SalesAid Software dguillett1@austin.rr.com "LurfysMa" <invalid@invalid.invalid> wrote in message news:o5fn83thi0g5tivf55abvhv...

Auto Open #2
Hello Everybody, I have Excel 2003 on WinXP SP2. I have copied a worksheet, "Invoice", into my Excel file called "Databased". Although, I have broken all links in the worksheet but still it tries to open the original file from where I copied the it. If the file is not found I get following error when "Databased" is opened: Cannot find 'C:\[ILYASBILLING Template (uw).xls]AutoOpen Stub Data'!, which has been assigned to run each time [Databased.xls]Invoice is opened. Continuing could cause errors. Cancel opening [Databased.xls]Invoice? Now I have no ...

Insert Existing Worksheets into a new Workbook
I have a set of many single page worksheets with calculations; I want to make a single Workbook using all of those pages. Then I want to extract data from a certain cell on each Worksheet and SUM that data on the last page of the Workbook. In EXCEL 2007 in order to extract and sum data on a specific page of the Workbook:- 1. Assume I have a Workbook set up in which there are 4 Worksheets and I wish to sum cell C6 from 3 of those Worksheets in Sheet1. 2. In cell C6 of Sheet1 (this is where I want the total to appear):- Home / Editing group / AutoSum / 3. Now click on ...

How do I combine two worksheets into one graph
I have two worksheets on an Excel spreadsheet. I would like to take both worksheets and place in one graph. Any suggestions? Make a chart with data from one sheet. Then copy the data on the other sheet, select the chart, and use Paste Special from the Edit menu to add the copied data as New Series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ McPowerUser wrote: > I have two worksheets on an Excel spreadsheet. I would like to take both > worksheets and place in one graph. Any suggestions? ...

How do I sort worksheet without affecting formula values?
I have two worksheets, one of which has formulas which reference the other. The problem is, I cannot sort the first worksheet and have the formula values in the second worksheet follow it so the values stay the same. I have tried making them absolute references ($A$1) but it doesn't help. I feel your pain. I've ran into the same issue before. The problem is the use of worksheet functions. The solution is DO NOT USE worksheet functions. Use a VBA subroutine instead. Here is an example: With worksheet functions.... A B ...

Save a single worksheet in Excel as a single file.
I have multipe worksheets in a single workbook. I would like to be able to save each worksheet individually. I want to send each worksheet to an individual, I do not want to send the entire workbook. All information must be retained in the one workbook, but as the individual worksheets are updated, the individual worksheet needs to be saved and sent to that individual. Help anyone? Thanks. Linda you can probably find the answer here. http://www.rondebruin.nl/ -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Dakota" <Dakota@discussions.microsoft.com&...

is it possible to Hide certain worksheets from certain users?
is it possible to Hide certain worksheets from certain users? if Yes which version of Excel offers this option? there is no built-in facility, you would have to build it all (and it is no simple). -- HTH RP (remove nothere from the email address if mailing direct) "Admin" <Admin@discussions.microsoft.com> wrote in message news:E10B3C1B-9E53-4927-BE3B-BE5C1AFFA223@microsoft.com... > is it possible to Hide certain worksheets from certain users? > > if Yes which version of Excel offers this option? > It requires using macros, which may or may not work for y...

Deleting groups of emails with auto-preview
I have a user that in Outlook Express he could hold down his shift key and start scrolling down and each email would show a preview as he scrolled down to determine which emails he wants to delete. I have upgraded him to OutlookXP and when he holds down the shift key and starts scrolling, just the first email is auto-previewed. Anyone know of a setting that would allow him to preview each message as he highlights and scrolls to the bottom? Thanks, Kevin ...

Continuous page numbers in workbook of multiple worksheets
I would like the page numbers to start with 1 on the first worksheet in a workbook and then have the next worksheet pick up with where the first worksheet left off. For example, if the first worksheet has 2 pages and the second worksheet in the workbook had 3 pages, then the first worksheet would contain pages 1 and 2 while the 2nd worksheet would contain pages 3, 4, and 5. Is there a way to have Excel figure out what the first page number should be in the 2nd worksheet or do I always have to manually update it in Page setup. THANKS! Hi Val Select all sheets first (right click on a...

making textbox to be auto sizable
I added a Textbox to a Word 2007 document by going to the Developer option(in the File menu), then chose Design Mode, then Legacy Tools->Text box Field. The MultiLine and Enterkey properties are set to True and the rest are default. The textbox is of size so that it can take some 10 rows of lines. Is there a feature I can choose/set so that when more than 10 rows of lines are entered the text box will automatically expand? The rows need to increase so the textbox needs to expand downwards to accommodate the text more than 10 lines. Can someone please advise if there is an inbuilt ...

Auto Loans
I have an auto with an associated loan, and I am purchasing a new car. I want to payoff the existing loan and transfer the remaining principle to the new asset (car)without it looking like the car cost more than I paid. The transaction consisted of paying off car1 and putting money down from a checking account along with the remainder coming from the new car loan. Money does not make this simple. Maybe it's not simple, but it is certainly possible. It may take several transactions. You might want to read the FAQ on mortgage refinances and extrapolate. "MikeF" <ano...

command buttons disappear when I move worksheet 2007
Excel 2007 - When I move my worksheet containg command buttons that run VB code to another workbook, the buttons disappear. How can I make the buttons move with the worksheet? I've never had this happen, but I use xl2003 much more than xl2007. You may want to give details of how you did the move. moulage wrote: > > Excel 2007 - When I move my worksheet containg command buttons that run VB > code to another workbook, the buttons disappear. How can I make the buttons > move with the worksheet? -- Dave Peterson ...

Auto generate a label/number
I would like to track orders and have it assign a new, consecutive order number to each new record. Example we have order MCO25, the next new record I would like to have it auto generate MCO26 hi, RA wrote: > I would like to track orders and have it assign a new, consecutive order > number to each new record. Example we have order MCO25, the next new record > I would like to have it auto generate MCO26 The 1000th order would be MCO1000? Try this: Public Funtion NextOrderNo() As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String strSQL =...

Why can't I enter any data on any new worksheets?
I cannot enter any information on any new, or existing worksheets. I've even tried uninstalling and reinstalling the program. Please help! ...