Solved

ADDING A NOTE SYMBOL TO AN EXCEL USERFORM

Posted on 2011-03-12
51
847 Views
Last Modified: 2012-05-11
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
Comment
Question by:FrankSasso
  • 24
  • 17
  • 9
51 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35115929
You mean this?

Label1.Caption = "¶"

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35115970
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
 

Author Comment

by:FrankSasso
ID: 35116005
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35116048
It depends on how are you adding the column header. :)

For exmaple

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

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35116056
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
 

Author Comment

by:FrankSasso
ID: 35118451
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35119138
Can you upload the planbuild estimator file? Let me check it personally.

Sid
0
 

Author Comment

by:FrankSasso
ID: 35119997
hi siddharthrout, file attached as requested
Planbuild-Estimator-2.5.0.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35120059
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35120064
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
 

Author Comment

by:FrankSasso
ID: 35120141
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35120146
>>> 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
 

Author Comment

by:FrankSasso
ID: 35120159
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35120171
>>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
 

Author Comment

by:FrankSasso
ID: 35121074
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35121114
Where can I get the CEEALib2.clsSimpleXML library if I want to test it?

Sid
0
 

Author Comment

by:FrankSasso
ID: 35121121
Hi Sid, will find out for you, wont be too long
0
 

Author Comment

by:FrankSasso
ID: 35121176
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35121185
Looking at it now.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35121187
Strange!

See the snapshot.

Sid
Untitled.jpg
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35121196
So it is working on my system. Try changing the font of listview to "Arial"

Sid
0
 

Author Comment

by:FrankSasso
ID: 35121211
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35121218
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
 

Author Comment

by:FrankSasso
ID: 35121247
hi sid, yes changed the font to arial, but still displayed square symbol, does this mean there is no solution?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35121251
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:FrankSasso
ID: 35121256
hi Sid, yes im sure i did mention at the beginning that this is in excel 2003 not 2007
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35121263
>>>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
 

Author Comment

by:FrankSasso
ID: 35121267
ok, sure Sid
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 500 total points
ID: 35121286
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
 

Author Comment

by:FrankSasso
ID: 35121320
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35121322
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
 

Author Comment

by:FrankSasso
ID: 35121342
ok, thanks sid please keep me posted wether there is or is not an alternative solution for me.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35121353
I will :)

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35121376
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35128069
Your workbook works fine for me in 2003 (that article does not relate to userforms).
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35128084
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35128109
Tested, and it displays fine on there too.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35128325
Hmmm that is strange. I have all updates of Office 2003 and XP. Could the OS play a part in this Rorya?

Sid
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35128344
Quite possible - both machines I tested on were WinXP SP3.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35128384
Ok I tested in

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

Sid
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35128481
Does it make any difference if you use ChrW(14) rather than Chr(14)?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35128498
And possibly change the font to Arial Unicode MS rather than Tahoma.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35128513
I tried that as well. No joy :(

Sid
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35128652
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
 

Author Comment

by:FrankSasso
ID: 35135517
Hi Rory and Sid, thankyou for your support, have tried your suggestion and still the note display is a square, thanks anyway.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35135523
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
 

Author Comment

by:FrankSasso
ID: 35135534
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35136610
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
 

Author Comment

by:FrankSasso
ID: 35141637
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35141690
I would think so - I just loaded a pic I found with a google search. ;)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query output to Excel producing error 7 41
TT Column Arrange 10 28
MIN, using ARRAY 4 17
Macro Filter fix 3 13
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now