• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1042
  • Last Modified:

ADDING A NOTE SYMBOL TO AN EXCEL USERFORM

in excel cntrl + 13 and cntrl + 14 brings up musical note symbol, how would i attach this symbol in an excel vba form in the label part of the form?
0
Frank .S
Asked:
Frank .S
  • 24
  • 17
  • 9
2 Solutions
 
SiddharthRoutCommented:
You mean this?

Label1.Caption = "¶"

Sid
0
 
SiddharthRoutCommented:
I might have misunderstood you. I think this is what you want? See snapshot attached.

Label1.Caption = Chr(14)

label1's font can be Arial / Tahoma or similar.

Sid
Untitled.jpg
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi siddenharthrout, yes i want the note image/ symbol but i want it on the location of the screenshot where you can see on the far right hand side of the label header in the form with the "?" character, so i basically want to replace the "?" with the image of alt +13, how can i do this please?
note-symbol-image---sample.xls
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
SiddharthRoutCommented:
It depends on how are you adding the column header. :)

For exmaple

ListView1.ColumnHeaders.Add Text:=Chr(14)

Sid
0
 
SiddharthRoutCommented:
Show me the code that you are using to add the column header in the listview.

See this snapshot.

The code that I am using is

    ListView1.ColumnHeaders.Add Text:="Group"
    ListView1.ColumnHeaders.Add Text:="Sub Group"
    ListView1.ColumnHeaders.Add Text:=Chr(14)

Open in new window


Sid
Untitled.jpg
0
 
Frank .SBuilding EstimatorAuthor Commented:
Hi SiddharthRout, used your code to display the note symbol however, what is displayed instead is a square not the note symbol? please see attached screenshot below. Please provide solution to assist me.
note-symbol---display-image-squa.xls
0
 
SiddharthRoutCommented:
Can you upload the planbuild estimator file? Let me check it personally.

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi siddharthrout, file attached as requested
Planbuild-Estimator-2.5.0.xls
0
 
SiddharthRoutCommented:
The problem is that you are not adding the column headers via code even though you have declared cColHeader at many places. I believe you are directly picking up the values of the header from the Excel sheet. I am unable to run it and test as the CEEALib2.clsSimpleXML library is missing.

However try this

In the userform activate event of the relevant form replace this

Private Sub UserForm_Activate()
    Application.Calculation = xlCalculationAutomatic
    loadData
End Sub

Open in new window


by

Private Sub UserForm_Activate()
    Application.Calculation = xlCalculationAutomatic
    loadData
    lvSelectedItemListMain.ColumnHeaders(12).Text = Chr(14)
End Sub

Open in new window


and tell me what happens.

Sid
0
 
SiddharthRoutCommented:
Also I have noticed that you are forcing the user to do a "save" or "save as" upon "exit". This might create a problem as what if I delete some data by mistake. I could have easily closed the file and re-opened it without saving but now I have to do a "save as" to avoid that?

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi sid, thankyou for your prompt reply, will try your steps and let you know what happens. As to your last comment, the reason for forcing user to save on exit is because i was concerned that if a user had been working on the programme for many hours and exited the prog without having saved it, their work would have been all lost, how else could that be prevented?
0
 
SiddharthRoutCommented:
>>> how else could that be prevented?

Give them a 3rd option in that box which let's them exit without saving as well. :)

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi sid, sorry to ask but i just dont seem to understand your logic, if the user does get a 3rd option to exit without saving, can they not risk losing their work? surely there needs to be something in place to avoid this from happening?
0
 
SiddharthRoutCommented:
>>can they not risk losing their work?

I am sure the user is matured enough to understand that when he/she clicks on the 3rd button, nothing will be saved. So that is 'OK' :)

Imagine yourself in place of that user. If you click on that 3rd button, you get a message that "Would you like to close the workbbook without saving? If yes then any changes made will be lost"

What would you choose if you want to exit without saving :)

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
Hi Sid, used your code;
 Private Sub UserForm_Activate()
    Application.Calculation = xlCalculationAutomatic
    loadData
    lvSelectedItemListMain.ColumnHeaders(12).Text = Chr(14)
End Sub

but the symbol is still displayed as a square, now what?
0
 
SiddharthRoutCommented:
Where can I get the CEEALib2.clsSimpleXML library if I want to test it?

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
Hi Sid, will find out for you, wont be too long
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi sid, with the new attached file you wont need a library, so please use new attached workbook and let me know
Planbuild-EstimatorR1.0.xls
0
 
SiddharthRoutCommented:
Looking at it now.

Sid
0
 
SiddharthRoutCommented:
Strange!

See the snapshot.

Sid
Untitled.jpg
0
 
SiddharthRoutCommented:
So it is working on my system. Try changing the font of listview to "Arial"

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi sid, could you send the workbook back to me if you made an adjustment so i can see if it now shows the note symbol on my pc?
0
 
SiddharthRoutCommented:
No. I didn't make any adjustment. I simply opened it and there it was :)

Did you try changing the font to Arial?

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi sid, yes changed the font to arial, but still displayed square symbol, does this mean there is no solution?
0
 
SiddharthRoutCommented:
Quick question. Are you opening this file in Excel 2003 or 2007. I tested it in 2007. If you have 2003 then I will have to reboot my laptop to test it in 2003.

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi Sid, yes im sure i did mention at the beginning that this is in excel 2003 not 2007
0
 
SiddharthRoutCommented:
>>>im sure i did mention at the beginning that this is in excel 2003 not 2007

Ummm, sorry, I re-read all the posts again and I can't see it. Maybe I am growing old :)

Anyways, rebooting. gimme 15 mins

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
ok, sure Sid
0
 
SiddharthRoutCommented:
Hmm. I just tested it and yes, it doesn't show in Excel 2003. I checked all the characters using this code.

Sub Sample()
    For i = 1 To 255 '<~~ You might have to check it in blocks of 50 i.e 1-50, 50-100 and so on
        Debug.Print i & ", " & Chr(i)
    Next i
End Sub

Open in new window


And that symbol is not there at all.

Let me rethink if there is an alternative.

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi sid, thanks for all your assistance I do appreciate it. Yes, please do suggest if there is an alternative or a way around it for excel 2003. I would have thought that if you can do it in 2007, that there should have been a solution for 2003 also, maybe not?
0
 
SiddharthRoutCommented:
FrankSasso: The symbol that you want is a unicode symbol. It's not easy displaying a unicode symbol in Userforms in Excel 2003. I am still working on it :)

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
ok, thanks sid please keep me posted wether there is or is not an alternative solution for me.
0
 
SiddharthRoutCommented:
I will :)

Sid
0
 
SiddharthRoutCommented:
Ummm, I just found this link for Office 2003.  :-(

http://office.microsoft.com/en-us/office-2003-resource-kit/unicode-support-and-multilingual-office-documents-HA001140229.aspx

The Microsoft Visual Basic® for Applications environment does not support Unicode. Only characters supported by the active Windows code page can be used in the Visual Basic Editor or displayed in custom dialog boxes or message boxes.

Sid
0
 
Rory ArchibaldCommented:
Your workbook works fine for me in 2003 (that article does not relate to userforms).
0
 
Rory ArchibaldCommented:
Having said that, it occurs to me that as I have 2007 also installed on this machine, there may have been a dll overwritten that allows the display. I will test on a standalone install of 2003.
0
 
Rory ArchibaldCommented:
Tested, and it displays fine on there too.
0
 
SiddharthRoutCommented:
Hmmm that is strange. I have all updates of Office 2003 and XP. Could the OS play a part in this Rorya?

Sid
0
 
Rory ArchibaldCommented:
Quite possible - both machines I tested on were WinXP SP3.
0
 
SiddharthRoutCommented:
Ok I tested in

XP SP3 Office 2003 SP3 - Didn't work
Vista Office 2007 - Worked
Win 7 Office 2010 - Worked

Sid
0
 
Rory ArchibaldCommented:
Does it make any difference if you use ChrW(14) rather than Chr(14)?
0
 
Rory ArchibaldCommented:
And possibly change the font to Arial Unicode MS rather than Tahoma.
0
 
SiddharthRoutCommented:
I tried that as well. No joy :(

Sid
0
 
Rory ArchibaldCommented:
Just to clarify: on my system there are two Arial Unicode fonts - one listed as '@Arial Unicode MS' and one as 'Arial Unicode MS'. The former does not work, the latter does.
I'm beginning to think it may be to do with the version of the common controls installed. Using Chrw(9834) does not work with the listview but it does with the native MSForms controls so clearly there is not full Unicode support. For me though Chrw(14) seems to work with all controls.
0
 
Frank .SBuilding EstimatorAuthor Commented:
Hi Rory and Sid, thankyou for your support, have tried your suggestion and still the note display is a square, thanks anyway.
0
 
SiddharthRoutCommented:
FrankSasso: There is some dll Missing in my pc and your pc since it works in Rory's pc. I cannot pinpoint which dll it is.

Sid
0
 
Frank .SBuilding EstimatorAuthor Commented:
Hi Sid,  i dont understand what you mean unfortunately as im no where near your level of IT language, however thats fine, thanks for trying to help me out, appreciate it.
0
 
Rory ArchibaldCommented:
What you could do is use an ImageList control too, and add a picture from that as your column header. Rough version attached.
Planbuild-EstimatorR1.0.zip
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi rorya, will try this and get back to you, by the way was there a single note instead of a double that could be used? the double is taking up a little bit too much space.
0
 
Rory ArchibaldCommented:
I would think so - I just loaded a pic I found with a google search. ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 24
  • 17
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now