A real challenge for you!!

[mevetts]

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]

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

 

[Doug Glancy]

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/2004/11/23/faceid-viewer-addin/

hth,

Doug

 

[mevetts]

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

 

[Guest]

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]

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.

 

[Bob Phillips]

Try using

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


--

HTH

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

 

[mevetts]

C'est bon! Merci monsieur.