Need a formula about time stamp....?

T

ToMMie

Hi all,

For example, A1 is column for date input and B1 is for the time date is
input in A1.

If I want excel to automatically show time the date is input, what formula
is used?

Now, i wrote one formula but it doesn't really fit my requirement.
I'm using like =if(a1<>"",now())
what I get at frist place seems ok but I just realized that by using this
formula, the time always changes.

Please help

ToMMie
 
G

Gary''s Student

You need a "frozen" time:

1. FIRST click on B1 and touch CNTRL-SHFT-:
2. Then enter stuff in A1
 
T

ToMMie

Thanks a lot.

Well I'm have another few more questions. If you could help me out, it would
be very kind of you.

1. to enter number in a cell, the lenght shouldn't be longer than 15 digits.
but I need to key in 1111222233334444 into a cell but I need an auto format
to display like 1111 2222 3333 4444. but the thing is that I format as custom
like 0000 0000 0000 0000 but it shows 1111 2222 3333 4440. (problem!!)

So, someone suggested me to key it as text and include spaces which is not
convenient to my users. Do you know any other ways to do this?


2. I groupped 5 columns. Then, I protect the worksheet. but I just can't
expand / collapse those groupped column. how can i fix that?

Million thanks.
ToMMie
 
G

Gary''s Student

This stumps me as well.

If you don't get a good answer to this post, I suggest you enter a new post
titled something like:

Formatting Text Input
 
G

Gord Dibben

!. You have many replies and suggestions for this at your other thread. I
can add nothing to those.

2. Enter this code in Thisworkbook module.

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.Protect Password:="justme", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

Note; the outliing and/or autofilter must be applied prior to saving and
re-opening.


Gord Dibben MS Excel MVP
 
T

ToMMie

Hi Dibben,

Could you please help me a bit on No. 2?
I'm not really sure whether or not I follow the right steps as I've never
done with VB before. According to your suggestion, I do, first, group columns
I want

1. go to Tools --> Macro --> Visual Basic Editor. It prompts me the windows
of MS Visual Basic
2. Then I double click This Workbook module. A new windows pops up
3. At drop down menu on left top, I select Workbook
4. Next one, I select Open
5. There is a message in below pane like
Private Sub Workbook_Open()

End Sub

6. I copy the code and paste it in between like
Private Sub Workbook_Open()
With Worksheets("Sheet1")
.Protect Password:="justme", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub
7. As I notice that font color of '.EnableAutoFilter = True turns green. I'm
not sure if there is a mistake? So I decide to delete ' and then it turns
black color which I think I do the right thing.
8. I protect worksheet then hit save and close
9. I close file and reopen it.
10. but I still can't do the expand or collapse the groupped columns.

PLease kindly help
ToMMie
 
G

Gord Dibben

You were correct in removing the ' from .EnableOutLining = True

The ' designates a non-running comment or remmed out line, in this case

As far as you not being able to expand/collapse the grouped
columns........the code works for me as advertised.

You sure the sheet with the outlining is Sheet1?

This is the only code you need in Thisworkbook.

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.Protect Password:="justme", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True 'not applicable in your case, I think
End With
End Sub


Gord
 
T

ToMMie

Hi Gord,

It works for me now. I really appreciate your kindness.
Well, I' m having another problem next to this one.
I share this workbook. and when I re-open it, it seems something error.

Please kindly help.

ToMMie
 
G

Gord Dibben

Shared workbooks have their own set of problems when it comes to protection
and the running of code on protected sheets.

I don't know how to code this into a shared workbook.

Hopefully someone has the proper code for this issue and will post it here.


Gord
 
Top