Excel button to enter time 2

[pmcnee]

We have an excel (2000) sheet used as time sheet to record start and finish times by the use of an excel button. You click the button and it enters in the cell next to it. However it does this by the code selecting the cell ref directly ie B5 = now() etc. And so we have a sheet with stacks of individual macros, the sheet also 31 tabs to cover the days of the mont, all this makes the sheet 2.3meg. I would like to keep the file size small

My question.. is there a way of when clicking the button that the time is entered in the cell to the left of itself? And so we could have many buttons calling on just one macro.

Many thanks

 

[xlbo]

Howzabout no macros and using

CTRL + ;
{SPACE}
CTRL + SHIFT + ;

?????

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

I really recommend Geoff's method ( I'd have said the same if he hadn't beaten me to it ).

BUT, if you already have loads of buttons embedded in the sheet, I can see why an alternative solution might be to your liking ... and that depends on what type of buttons you have. Are they Forms Toolbar type buttons, or Control Toolbox type buttons?

With a Forms Toolbar type button you can use code like this to find out where the button is located that called the routine ( and have just the one routine for all buttons )...

    buttonname = Application.Caller
    cellwhere = ActiveSheet.DrawingObjects(buttonname).TopLeftCell.Address

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[pmcnee]

Thanks Xlbo, I'll remember those shortcuts for another time. This particular sheet will be used by people who for some of them, remembering to click a button is an issue! and so a shortcut of more than two keys will be a bit too tricky!

I'm going to give GlennUk's suggestion a go, thanks Glenn


Cheers pmcnee

 

[pmcnee]

Me again.. Sorry Glenn, i'm a bit of novice on this code melarky. I've had a go but no joy. Any chance you could walk me through? my button name is TimeButton (Form type button) and the time needs to go in the cell to the buttons left. Here's how far i got

TimeButton = Application.Caller
cellwhere = ActiveSheet.DrawingObjects(TimeButton).TopLeftCell.Address

    
Sub EnterTime()

cellwhere.Offset(0, 1) = Now()

End Sub

Many thanks

pmcnee

 

[GlennUK]

Sub EnterTime()
TimeButton = Application.Caller
ActiveSheet.DrawingObjects(TimeButton).TopLeftCell.Offset(0, -1) = Now()
End Sub

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[pmcnee]

Fantastic Gleen, works a treat.

Many thanks

pmcnee

 

[GlennUK]

My pleasure

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[arthurbr]

Pfewww, someone said I was good at Excel, but these guys are more than outstanding!!
Congratulations from Belgium !

 

[GlennUK]

Thanks arthurbr ( or is it André? ), and thank you to Belgium for all of those beers ( I'll get through them all one day )



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.