Hi,

This is probably my most difficult section for the current workbook
that I'm producing.

To set the scene, I'm a teacher and I am creating a register and
markbook in excel. The reason being is that I'm aiming to be able to
actually use the data far more than if it was written down in on
paper.

However, I face a major hurdle. The register is taken during every
lesson and I personally like to undertake this process at the very
beginning of the lesson. The process needs to be very quick and
efficient so that the lesson can get underway as soon as possible.

At the moment I work with three possible options for a pupil - ontime,
late or absent. However, the hope is that this spreadsheet may get
adopted by other members of staff and they may want to tailor the
options to meet their requirements.

I have tried simply copying and pasting from a key of symbols to record
the info, but this proved too fidly and time consuming.

Validation also turned out to be too slow and the inability to display
the actually symbols in the drop down list made it next too useless.

I have tried out creating macros and assigning them to buttons and this
has proved out to be the most successful so far.

However, I have seen (and downloaded) a spreadsheet that allows a
simple click to input a tick into a column. It was very quick and
worked well. I was wondering if the code could perhaps be adapted in
some way to meet the results I want.

Can the number of clicks determine the symbol i.e. it sort of scrolls
through the possible options with a left mouse click and a double click
moves it to the next cell down?

Anyway, here's the code I downloaded -

Code written by Nick Hodge
15/11/04
Contained in this worksheet's code module
It detects a click in columns D or E and then inserts a tick mark. It
then moves to column G in the same row. Clicking on a tick, removes it
and moves you to column G

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iOffset As Integer
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("D:E")) Is Nothing Then
If Target.Column = 4 Then
iOffset = 3
Else
iOffset = 2
End If
If IsEmpty(Target.Value) Then
With Target
.Font.Name = "Wingdings"
.Value = Chr(252)
End With
Target.Offset(0, iOffset).Select
Else
Target.Value = ""
Target.Offset(0, iOffset).Select
End If
End If
err_handler:
Application.EnableEvents = True
End Sub

Any help would be great and I know some of you enjoy a challenge!

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

presumably you will mark the absent students as absent,and modify that to
late if the arrive later?How many students?,what about three radio buttons
per line marked present absent late?

hope this helps
paul
remove nospam for email addy!



"mevetts" wrote:


Hi,

This is probably my most difficult section for the current workbook
that I'm producing.

To set the scene, I'm a teacher and I am creating a register and
markbook in excel. The reason being is that I'm aiming to be able to
actually use the data far more than if it was written down in on
paper.

However, I face a major hurdle. The register is taken during every
lesson and I personally like to undertake this process at the very
beginning of the lesson. The process needs to be very quick and
efficient so that the lesson can get underway as soon as possible.

At the moment I work with three possible options for a pupil - ontime,
late or absent. However, the hope is that this spreadsheet may get
adopted by other members of staff and they may want to tailor the
options to meet their requirements.

I have tried simply copying and pasting from a key of symbols to record
the info, but this proved too fidly and time consuming.

Validation also turned out to be too slow and the inability to display
the actually symbols in the drop down list made it next too useless.

I have tried out creating macros and assigning them to buttons and this
has proved out to be the most successful so far.

However, I have seen (and downloaded) a spreadsheet that allows a
simple click to input a tick into a column. It was very quick and
worked well. I was wondering if the code could perhaps be adapted in
some way to meet the results I want.

Can the number of clicks determine the symbol i.e. it sort of scrolls
through the possible options with a left mouse click and a double click
moves it to the next cell down?

Anyway, here's the code I downloaded -

Code written by Nick Hodge
15/11/04
Contained in this worksheet's code module
It detects a click in columns D or E and then inserts a tick mark. It
then moves to column G in the same row. Clicking on a tick, removes it
and moves you to column G

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iOffset As Integer
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("D:E")) Is Nothing Then
If Target.Column = 4 Then
iOffset = 3
Else
iOffset = 2
End If
If IsEmpty(Target.Value) Then
With Target
.Font.Name = "Wingdings"
.Value = Chr(252)
End With
Target.Offset(0, iOffset).Select
Else
Target.Value = ""
Target.Offset(0, iOffset).Select
End If
End If
err_handler:
Application.EnableEvents = True
End Sub

Any help would be great and I know some of you enjoy a challenge!

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

It is a possibility, but the only issue will be that if a teacher has
twenty or so pupils then it could become very crowded on the screen.

I want to keep the workbook as clean as possible as I believe this will
facilitate easy of use.

Cheers,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Here is a method that just cycles through the 3 symbols on selecting the
cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iOffset As Integer
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("D:E")) Is Nothing Then
With Target
.Font.Name = "Marlett"
Select Case .Value
Case "", "r": .Value = "a"
Case "", "a": .Value = "p"
Case "", "p": .Value = "r"
End Select
.Offset(0, 1).Select
End With
End If
err_handler:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"mevetts" wrote in
message ...

Hi,

This is probably my most difficult section for the current workbook
that I'm producing.

To set the scene, I'm a teacher and I am creating a register and
markbook in excel. The reason being is that I'm aiming to be able to
actually use the data far more than if it was written down in on
paper.

However, I face a major hurdle. The register is taken during every
lesson and I personally like to undertake this process at the very
beginning of the lesson. The process needs to be very quick and
efficient so that the lesson can get underway as soon as possible.

At the moment I work with three possible options for a pupil - ontime,
late or absent. However, the hope is that this spreadsheet may get
adopted by other members of staff and they may want to tailor the
options to meet their requirements.

I have tried simply copying and pasting from a key of symbols to record
the info, but this proved too fidly and time consuming.

Validation also turned out to be too slow and the inability to display
the actually symbols in the drop down list made it next too useless.

I have tried out creating macros and assigning them to buttons and this
has proved out to be the most successful so far.

However, I have seen (and downloaded) a spreadsheet that allows a
simple click to input a tick into a column. It was very quick and
worked well. I was wondering if the code could perhaps be adapted in
some way to meet the results I want.

Can the number of clicks determine the symbol i.e. it sort of scrolls
through the possible options with a left mouse click and a double click
moves it to the next cell down?

Anyway, here's the code I downloaded -

Code written by Nick Hodge
15/11/04
Contained in this worksheet's code module
It detects a click in columns D or E and then inserts a tick mark. It
then moves to column G in the same row. Clicking on a tick, removes it
and moves you to column G

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iOffset As Integer
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("D:E")) Is Nothing Then
If Target.Column = 4 Then
iOffset = 3
Else
iOffset = 2
End If
If IsEmpty(Target.Value) Then
With Target
Font.Name = "Wingdings"
Value = Chr(252)
End With
Target.Offset(0, iOffset).Select
Else
Target.Value = ""
Target.Offset(0, iOffset).Select
End If
End If
err_handler:
Application.EnableEvents = True
End Sub

Any help would be great and I know some of you enjoy a challenge!

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Mark,

I've been involved in register packages in the past. I think the
quickest approach is to assume all pupils are present (i.e. have a
"Fill Present" option/button) and then just mark the pupils who are
absent. If they then subsequently turn up late, it's easy enough (if
you remember at the time) to change their absence to a late mark.

Just a thought ...

Pete

RP - Thanks, this is a help because I can now see how the code would go
if I decide to take that route.

Pete - I think you might be right and I believe one of my colleagues
who has been helping develop the workbook took this approach with his
trialing.

I have this code already in place which inserts today's date at the top
of the column -

Public Sub Date_Today()
With ActiveCell
.Value = Date
.NumberFormat = "dd-mmm-yy"
End With
ActiveCell.Offset(1, 0).Select
End Sub

Could this be extended so that it put a tick or letter or symbol next
to each pupil?

The code would need to look and see if there is a number in column A
(each pupil is numbered) and put a tick in the corresponding cell. It
would continue down the list until there was a blank in column A.

Can anyone achieve this?

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

I think Pete's is a good approach

for the entry of the tickmarks why not create a commandbar?
put code in a NORMAL module

Option Explicit

Sub BuildBar()
Const BARNAME = "TickMarks"
With Application
On Error Resume Next
.CommandBars(BARNAME).Delete
On Error GoTo 0
With .CommandBars.Add(BARNAME, msoBarTop, , True)
With .Controls.Add(, , 1, , True)
.Caption = "OnTime"
.FaceId = 1087
.OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
With .Controls.Add(, , 2, , True)
.Caption = "Absent"
.FaceId = 1088
.OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
With .Controls.Add(, , 3, , True)
.Caption = "Late"
.FaceId = 1089
.OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
.Visible = True
End With
End With
End Sub

Public Sub ButtonHandler()
Dim sWD$
sWD = Chr$(Choose(Application.CommandBars.ActionControl. Parameter, _
252, 251, 220))
With ActiveCell
With .EntireRow.Cells(1)
.Font.Name = "Wingdings"
.Value = sWD
End With
.Offset(1).Activate
End With
End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Pete wrote :

Mark,

I've been involved in register packages in the past. I think the
quickest approach is to assume all pupils are present (i.e. have a
"Fill Present" option/button) and then just mark the pupils who are
absent. If they then subsequently turn up late, it's easy enough (if
you remember at the time) to change their absence to a late mark.

Just a thought ...

Pete

:( .....I'm what you call a novice at all this and that really just went
over my head!

Could you explain that in a little more plain English for me?


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Is it something to do with creating a custom toolbar which is attached
to a specfic workbook?


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

You were posting code earlier, so I assume
you know how to create a module and copy/paste the code into that..

Why dont you give it a try...

the macro creates a temporary toolbar.
(gone when you restart excel)

it could be called from the Workbook_Open event
so it loads when the workbook opens.
and similarly could be deleted on workbook_beforeclose

But that is all secondary...
First thing.. does using a toolbar to enter the ticks work for you.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


mevetts wrote :


:( .....I'm what you call a novice at all this and that really just
went over my head!

Could you explain that in a little more plain English for me?

Public Sub Date_Today()
Dim iLastrow As Long
Dim i As Long
Dim nHeight As Double

iLastrow = Cells(Rows.Count, "A").End(xlUp).Row

With ActiveCell
Cells(1, .Column).Value = Date
Cells(1, .Column).NumberFormat = "dd-mmm-yy"
For i = 2 To iLastrow
With Cells(i, .Column)
nHeight = .EntireRow.Height
.Value = "p"
.Font.Name = "Marlett"
.EntireRow.RowHeight = nHeight
End With
Next i
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mevetts" wrote in
message ...

RP - Thanks, this is a help because I can now see how the code would go
if I decide to take that route.

Pete - I think you might be right and I believe one of my colleagues
who has been helping develop the workbook took this approach with his
trialing.

I have this code already in place which inserts today's date at the top
of the column -

Public Sub Date_Today()
With ActiveCell
Value = Date
NumberFormat = "dd-mmm-yy"
End With
ActiveCell.Offset(1, 0).Select
End Sub

Could this be extended so that it put a tick or letter or symbol next
to each pupil?

The code would need to look and see if there is a number in column A
(each pupil is numbered) and put a tick in the corresponding cell. It
would continue down the list until there was a blank in column A.

Can anyone achieve this?

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Hi,

Bob - I tested your code, but it went a bit mad when I ran the macro.
It put symbols in all the cells in that column (below the active cell),
rather than stopping at the end of the pupil list in that particular
class register.

KeepITcool - I have put the code in and it creates the toolbar when the
macro is run. However, the symbol is placed in column A, rather than the
active cell?

Can the code be altered to rectify this?

Also, it is possible to develop a button the will put a tick next to
all pupils in the particular register on the screen? The macro needs to
look and see if there is a number in column A, if there is put a tick in
the corresponding cell in the column being used for that particular days
register.

I've attached a screenshot of an example register so that it may help
explain what I'm working with and trying to achieve!

Any help would be really appreciated.

Mark.


+-------------------------------------------------------------------+
|Filename: screen.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4158 |
+-------------------------------------------------------------------+

--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

"mevetts" wrote in
message ...

Hi,

Bob - I tested your code, but it went a bit mad when I ran the macro.
It put symbols in all the cells in that column (below the active cell),
rather than stopping at the end of the pupil list in that particular
class register.

Probably because I tested column A for the pupil numbers. If it is somewhere
else the coide should be modified.

KeepITcool - I have put the code in and it creates the toolbar when the
macro is run. However, the symbol is placed in column A, rather than the
active cell?

Can the code be altered to rectify this?

Public Sub ButtonHandler()
Dim sWD$
sWD = Chr$(Choose(Application.CommandBars.ActionControl. Parameter, _
252, 251, 220))
With ActiveCell
.Font.Name = "Wingdings"
.Value = sWD
.Offset(1).Activate
End With
End Sub


Also, it is possible to develop a button the will put a tick next to
all pupils in the particular register on the screen? The macro needs to
look and see if there is a number in column A, if there is put a tick in
the corresponding cell in the column being used for that particular days
register.

That was what the code I gave does. I fail to understand what you say it
does.

Hi Bob,

When I ran the macro it put symbols in, but rather than stopping at the
end of the class list, it jumped down to the next class and carried on
placing symbols next to each of their names as well.

I would like it to stop placing the symbol in each row at the end of
the list of names for that particular class. That is why I want it to
check if there is a number in column A, if there is then insert the
symbol, if there isn't then the macro should end.

See my screen shot to get an idea of what I mean.

Cheers,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Try this. You need to select a cell in the sub-heading row, 56 in your
example

Public Sub Date_Today()
Dim iLastrow As Long
Dim i As Long
Dim nHeight As Double

iLastrow = Cells(Activecell.Row, "A").End(xlDown).Row

With ActiveCell
Cells(1, .Column).Value = Date
Cells(1, .Column).NumberFormat = "dd-mmm-yy"
For i = 2 To iLastrow
With Cells(i, .Column)
nHeight = .EntireRow.Height
.Value = "p"
.Font.Name = "Marlett"
.EntireRow.RowHeight = nHeight
End With
Next i
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"mevetts" wrote in
message ...

Hi Bob,

When I ran the macro it put symbols in, but rather than stopping at the
end of the class list, it jumped down to the next class and carried on
placing symbols next to each of their names as well.

I would like it to stop placing the symbol in each row at the end of
the list of names for that particular class. That is why I want it to
check if there is a number in column A, if there is then insert the
symbol, if there isn't then the macro should end.

See my screen shot to get an idea of what I mean.

Cheers,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Hi Bob,

That code doesn't seem to work either. If I select the cell where the
date should be inserted and click the date button a symbol is entered
rather than today's date. Then all the cells above the active cel in
the same column have the symbol entered.

If however, I move the one row down next to the row that corresponds to
the first pupil in the list and run the macro, then it does put a symbol
next to each of the pupils in that list and it does stop at the last
pupil.

Not sure what needs to be altered?

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Public Sub Date_Today()
Dim iLastrow As Long
Dim i As Long
Dim nHeight As Double

iLastrow = Cells(ActiveCell.Row, "A").End(xlDown).Row

With ActiveCell
Cells(.Row, .Column).Value = Date
Cells(.Row, .Column).NumberFormat = "dd-mmm-yy"
Cells(.Row, .Column).Font.Name = "Arial"
For i = .Row + 1 To iLastrow
With Cells(i, .Column)
nHeight = .EntireRow.Height
.Value = "a"
.Font.Name = "Marlett"
.EntireRow.RowHeight = nHeight
End With
Next i
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"mevetts" wrote in
message ...

Hi Bob,

That code doesn't seem to work either. If I select the cell where the
date should be inserted and click the date button a symbol is entered
rather than today's date. Then all the cells above the active cel in
the same column have the symbol entered.

If however, I move the one row down next to the row that corresponds to
the first pupil in the list and run the macro, then it does put a symbol
next to each of the pupils in that list and it does stop at the last
pupil.

Not sure what needs to be altered?

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Hi Bob,

All it does now is just put the date in the active cell. It's not even
moving down to the next row.

Not sure what too do.

I've tried looking at the code, but it is still not making a huge
amount of sense to me yet.

Do you think you could take a look.

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

It's the damn merged cells, it takes the cells merged in as empty cells, so
nowhere to populate. Get rid of them, they are more trouble le than they
are worth.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mevetts" wrote in
message ...

Hi Bob,

All it does now is just put the date in the active cell. It's not even
moving down to the next row.

Not sure what too do.

I've tried looking at the code, but it is still not making a huge
amount of sense to me yet.

Do you think you could take a look.

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Bob,

I removed the merge, but the same thing happened, although it did move
down to the next row this time, but then stopped.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Hi,

This post is primarily aimed at keepITcool, but please, anyone could
try to help me out!

The code for creating the command bar contained some face id values,
which I presume say what symbol to display on the bar? How can I find
what codes relate to what symbols? I've looked on the web, but can't
locate a key anywhere?

Also, can the code be altered so that on the 'Late' button it colours
the background of the cell as well? The reason being that I want to use
a tick for both the on time pupils and the late pupils, but highlight
the lates with a coloured background.

Here's the code -

Option Explicit

Sub BuildBar()
Const BARNAME = "TickMarks"
With Application
On Error Resume Next
.CommandBars(BARNAME).Delete
On Error GoTo 0
With .CommandBars.Add(BARNAME, msoBarTop, , True)
With .Controls.Add(, , 1, , True)
.Caption = "OnTime"
.FaceId = 1087
.OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
With .Controls.Add(, , 2, , True)
.Caption = "Absent"
.FaceId = 1088
.OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
With .Controls.Add(, , 3, , True)
.Caption = "Late"
.FaceId = 1089
.OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
.Visible = True
End With
End With
End Sub

Public Sub ButtonHandler()
Dim sWD$
sWD = Chr$(Choose(Application.CommandBars.ActionControl. Parameter, _
252, 251, 220))
With ActiveCell
.Font.Name = "Wingdings"
.Value = sWD
.Offset(1).Activate
End With
End Sub

Any help would be just brill!

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

I have updated the code so now it has an exclamation mark for 'Late' and
I changed the order around of the buttons.

I now also have the same symbol for both 'on time' and 'late'.

How can I change the code so that when late is clicked it changes the
background colour as well?

Here's the updated code -

Sub BuildBar()
Const BARNAME = "TickMarks"
With Application
On Error Resume Next
.CommandBars(BARNAME).Delete
On Error GoTo 0
With .CommandBars.Add(BARNAME, msoBarTop, , True)
With .Controls.Add(, , 1, , True)
.Caption = "OnTime"
.FaceId = 1087
.OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
With .Controls.Add(, , 2, , True)
.Caption = "Late"
.FaceId = 964
.OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
With .Controls.Add(, , 3, , True)
.Caption = "Absent"
.FaceId = 1088
.OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
.Visible = True
End With
End With
End Sub

Public Sub ButtonHandler()
Dim sWD$
sWD = Chr$(Choose(Application.CommandBars.ActionControl. Parameter, _
252, 252, 251))
With ActiveCell
.Font.Name = "Wingdings"
.Value = sWD
.Offset(1).Activate
End With
End Sub


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

mevetts,

In answer to your first question, you can find sever faceid viewers on the
web. I've got one at this link:

http://www.dicks-blog.com/archives/2...-viewer-addin/

hth,

Doug


"mevetts" wrote in
message ...

Hi,

This post is primarily aimed at keepITcool, but please, anyone could
try to help me out!

The code for creating the command bar contained some face id values,
which I presume say what symbol to display on the bar? How can I find
what codes relate to what symbols? I've looked on the web, but can't
locate a key anywhere?

Also, can the code be altered so that on the 'Late' button it colours
the background of the cell as well? The reason being that I want to use
a tick for both the on time pupils and the late pupils, but highlight
the lates with a coloured background.

Here's the code -

Option Explicit

Sub BuildBar()
Const BARNAME = "TickMarks"
With Application
On Error Resume Next
CommandBars(BARNAME).Delete
On Error GoTo 0
With .CommandBars.Add(BARNAME, msoBarTop, , True)
With .Controls.Add(, , 1, , True)
Caption = "OnTime"
FaceId = 1087
OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
With .Controls.Add(, , 2, , True)
Caption = "Absent"
FaceId = 1088
OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
With .Controls.Add(, , 3, , True)
Caption = "Late"
FaceId = 1089
OnAction = ThisWorkbook.Name & "!buttonhandler"
End With
Visible = True
End With
End With
End Sub

Public Sub ButtonHandler()
Dim sWD$
sWD = Chr$(Choose(Application.CommandBars.ActionControl. Parameter, _
252, 251, 220))
With ActiveCell
Font.Name = "Wingdings"
Value = sWD
Offset(1).Activate
End With
End Sub

Any help would be just brill!

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Here's the new look code for anyone that's interested -

Sub BuildBar()
Const BARNAME = "TickMarks"
With Application
On Error Resume Next
.CommandBars(BARNAME).Delete
On Error GoTo 0
With .CommandBars.Add(BARNAME, msoBarTop, , True)
With .Controls.Add(, , 1, , True)
.Caption = "OnTime"
.FaceId = 1087
.OnAction = "ButtonHandler"
End With
With .Controls.Add(, , 2, , True)
.Caption = "Late"
.FaceId = 964
.OnAction = "ButtonHandler"
End With
With .Controls.Add(, , 3, , True)
.Caption = "Absent"
.FaceId = 1088
.OnAction = "ButtonHandler"
End With
.Visible = True
End With
End With
End Sub

Public Sub ButtonHandler()
Dim sButtonClicked As String
sButtonClicked = Application.CommandBars.ActionControl.Caption
With ActiveCell
.Font.Name = "Wingdings"
Select Case sButtonClicked
Case "OnTime"
.Value = Chr(252)
'.Interior.ColorIndex = 43
Case "Late"
.Interior.ColorIndex = 40
.Value = Chr(252)
Case "Absent"
'.Interior.ColorIndex = 3
.Value = Chr(251)
End Select
.Offset(1).Activate
End With
End Sub


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Hello,
How about a simple userform with a list of students. When you click the
students name it appears in a label with the 3 optionbuttons next to it. Here
you can select your option. A button to confirm your selection then the
given info is sent to the sheet corresponding to the students name.

- -Mark
http://www.geocities.com/excelmarksway




"mevetts" wrote:


Hi,

This is probably my most difficult section for the current workbook
that I'm producing.

To set the scene, I'm a teacher and I am creating a register and
markbook in excel. The reason being is that I'm aiming to be able to
actually use the data far more than if it was written down in on
paper.

However, I face a major hurdle. The register is taken during every
lesson and I personally like to undertake this process at the very
beginning of the lesson. The process needs to be very quick and
efficient so that the lesson can get underway as soon as possible.

At the moment I work with three possible options for a pupil - ontime,
late or absent. However, the hope is that this spreadsheet may get
adopted by other members of staff and they may want to tailor the
options to meet their requirements.

I have tried simply copying and pasting from a key of symbols to record
the info, but this proved too fidly and time consuming.

Validation also turned out to be too slow and the inability to display
the actually symbols in the drop down list made it next too useless.

I have tried out creating macros and assigning them to buttons and this
has proved out to be the most successful so far.

However, I have seen (and downloaded) a spreadsheet that allows a
simple click to input a tick into a column. It was very quick and
worked well. I was wondering if the code could perhaps be adapted in
some way to meet the results I want.

Can the number of clicks determine the symbol i.e. it sort of scrolls
through the possible options with a left mouse click and a double click
moves it to the next cell down?

Anyway, here's the code I downloaded -

Code written by Nick Hodge
15/11/04
Contained in this worksheet's code module
It detects a click in columns D or E and then inserts a tick mark. It
then moves to column G in the same row. Clicking on a tick, removes it
and moves you to column G

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iOffset As Integer
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("D:E")) Is Nothing Then
If Target.Column = 4 Then
iOffset = 3
Else
iOffset = 2
End If
If IsEmpty(Target.Value) Then
With Target
.Font.Name = "Wingdings"
.Value = Chr(252)
End With
Target.Offset(0, iOffset).Select
Else
Target.Value = ""
Target.Offset(0, iOffset).Select
End If
End If
err_handler:
Application.EnableEvents = True
End Sub

Any help would be great and I know some of you enjoy a challenge!

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Sorry to resurrect this thread, but a little issue has arisen!


I'm using this bit of code for the buttons on my command bar -


Code:
--------------------
Public Sub ButtonHandler()
Dim sButtonClicked As String
sButtonClicked = Application.CommandBars.ActionControl.Caption
With ActiveCell
.Font.Name = "Wingdings"
Select Case sButtonClicked
Case "OnTime"
.Value = Chr(252)
.Interior.ColorIndex = 0
Case "Late"
.Interior.ColorIndex = 43
.Value = Chr(186)
Case "Absent"
.Interior.ColorIndex = 3
.Value = Chr(251)
Case "Bookless"
.Interior.ColorIndex = 17
.Value = Chr(38)
Case "LateBooks"
.Interior.ColorIndex = 39
.Value = Chr(37)
Case "Reset"
.Interior.ColorIndex = 0
.Value = Chr(32)
End Select
.Offset(1).Activate
End With
End Sub
--------------------


As you can see I have a button titled 'Reset'. This is intended to just
clear a cell of anything.

But, it seems that it is not clearing the cell, I have some formulas
working and if I use the button to remove info from a cell, the formula
is still thinking that the cell contains something. But when I highlight
the cells and press the delete key the formula updates.

Can the code be updated so when the button is clicked everything is
removed from the cell i.e. the symbol and the background colour?

Many thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

Try using

Case "Reset"
.Interior.ColorIndex = xlcolorindexnone
.Clearcontents


--

HTH

RP
(remove nothere from the email address if mailing direct)


"mevetts" wrote in
message ...

Sorry to resurrect this thread, but a little issue has arisen!


I'm using this bit of code for the buttons on my command bar -


Code:
--------------------
Public Sub ButtonHandler()
Dim sButtonClicked As String
sButtonClicked = Application.CommandBars.ActionControl.Caption
With ActiveCell
.Font.Name = "Wingdings"
Select Case sButtonClicked
Case "OnTime"
.Value = Chr(252)
.Interior.ColorIndex = 0
Case "Late"
.Interior.ColorIndex = 43
.Value = Chr(186)
Case "Absent"
.Interior.ColorIndex = 3
.Value = Chr(251)
Case "Bookless"
.Interior.ColorIndex = 17
.Value = Chr(38)
Case "LateBooks"
.Interior.ColorIndex = 39
.Value = Chr(37)
Case "Reset"
.Interior.ColorIndex = 0
.Value = Chr(32)
End Select
.Offset(1).Activate
End With
End Sub
--------------------


As you can see I have a button titled 'Reset'. This is intended to just
clear a cell of anything.

But, it seems that it is not clearing the cell, I have some formulas
working and if I use the button to remove info from a cell, the formula
is still thinking that the cell contains something. But when I highlight
the cells and press the delete key the formula updates.

Can the code be updated so when the button is clicked everything is
removed from the cell i.e. the symbol and the background colour?

Many thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

C'est bon! Merci monsieur.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852