Button to insert date and button colour change 1

[realbeginner]

Hi. I'm making an EXCEL register with buttons to use on an interactive whiteboard in the classroom. I'm almost there but I'm stuck on the last two bits:
- Could you show me how to click a button to input today's date so that it stays as that date (i.e. not a function)
- Could you see what I've done wrong in the code below which should just change the colour of the button:

CODE
Sub Button1_Click()
'
' Button1_Click Macro Present

With ActiveCell
.Formula = "1"
.Offset(1, 0).Select
End With
Button1.ForeColor = RGB(0, 255, 0)
' Colour - Green
End Sub


Many thanks!

 

[TonyJollans]

Hi realbeginner,

With your code as posted, just change ..
[purple][tt] .Formula = "1"[/tt][/purple]
to
[blue][tt] .Formula = Date[/tt][/blue]

Not sure what's wrong with the colouring code - it works for me. It does change the color of the text on the button not the button itself so maybe you just want BackColor instead of ForeColor.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[realbeginner]

Hi. I'm still having problems with the colour of the button. The following CODE gives a grey box which says "Run-time error '424' Object required" Debug highlights the Button1.BackColour line. Any ideas?

Sub Button1_Click()
'
' Button1_Click Macro Present

With ActiveCell
.Formula = "1"
.Offset(1, 0).Select
End With
Button1.BackColor = RGB(0, 255, 0)
' Colour - Green
End Sub

 

[TonyJollans]

Hi realbeginner,

It seems you have a button from the Forms Toolbar. I would change it to one from the Control Toolbox.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[realbeginner]

Many thanks Tony. That worked a treat. I'll use the new register in class tomorrow.

Best wishes,

Realbeginner