0

edit: all responses have missed my point, so i've removed any mention of special characters and just want to focus on the line break vbCrLf

i have a combobox populated from a range of cells, and i want a msgbox to pop up based on the user's selection in said combobox, such that the msgbox text is taken from a cell which corresponds to the user's selected cell (i.e. the cell linked to the user's cbox selection). for example:

user selection msgbox text
scarf It's 40 degrees.
Are you sure you want a scarf?
swimshorts Don't forget your sunscreen!

so that's the reason for trying to fill the cbox from a cell, rather than from VB.

my problem is how to write the newline and special character in the reference cell and have msgbox format it correctly.

i.e. MsgBox "It's 40 degrees." & vbCrLf & "Are you sure you want a scarf?" does what i want, but if i place that text in a cell and then try to reference that cell with a msgbox, e.g. MsgBox Sheet1.Range("B2"), then the msgbox just prints the text without formatting.

e.g. https://i.stack.imgur.com/A9hVH.jpg

please, any help here would be greatly appreciated

Mike Rotch
  • 3
  • 2
  • thanks, braX. i tried that, but the msgbox still displayed the string as written without any formatting, i.e. "It's 40" & ChrW(8451) & vbCrLf & "Are you sure you want a scarf?" – Mike Rotch Nov 01 '21 at 09:10
  • https://stackoverflow.com/questions/55210315/how-do-i-display-a-messagebox-with-unicode-characters-in-vba – braX Nov 01 '21 at 09:17
  • that's different. if i directly call `MsgBox ChrW(8451)`, i get the degree C symbol, but if i put `ChrW(8451)` in, say, cell `A1`, and then `MsgBox [A1]`, my messagebox comes up with "ChrW(8451)" – Mike Rotch Nov 01 '21 at 09:28
  • @MikeRotch try the answer that braX posted, remember to change to `Application.hWnd` as mentioned in the answer. – Raymond Wu Nov 01 '21 at 10:14
  • In Excel, the function to return unicode characters from their "number" is `UNICHAR`. But `MSGBOX` is not compatible with those characters so you need a different solution. – Ron Rosenfeld Nov 01 '21 at 15:19
  • @MikeRotch *"if i directly call MsgBox ChrW(8451), i get the degree C symbol* What version of Excel? If I do that (from the immediate window in the VBA editor), I get the `?` – Ron Rosenfeld Nov 01 '21 at 17:40
  • @RonRosenfeld Excel2019... but the special characters weren't really the problem. i have since edited my post (and in the meanwhile, i see that you have already answered it). just out of curiosity, what about `MsgBox ChrW(176)` ? i only ask because it seems i may need to consider the characters available to other users, seeing as `ChrW(8451)` didn't work for you – Mike Rotch Nov 02 '21 at 06:01
  • Not at computer, but you can check it easily enough. – Ron Rosenfeld Nov 02 '21 at 06:09

1 Answers1

0

To add a line feed in a cell, if you are entering it from the worksheet:

A1: ="abc" & CHAR(10) & "def"

For the linefeed character, you can type abcalt+enterdef

enter image description here

If you are entering it from VBA, then the code line would be:

Range("A1")  = "abc" & vblf & "def"
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • my sincerest thanks, entering `="abc" & CHAR(10) & "def"` in the worksheet cell worked perfectly, while `abc` `alt+010` `def` did not work for me either. on the other hand, `abc` `Alt+Enter` `def` (no `=` or `"`) also worked perfectly. *edited because tags don't work in comments – Mike Rotch Nov 02 '21 at 06:24
  • ps. sorry i don't have enough reputation to upvote – Mike Rotch Nov 02 '21 at 06:26
  • 1
    @MikeRotch See my edit for entering the linefeed character from the keyboard -- brain cramp last night. It's `alt-enter`, not `alt-0010` – Ron Rosenfeld Nov 02 '21 at 11:39