• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Message boxes with cell values

E

excel_stuff

Member
Hi,

I need a macro that does the following (and would appreciate your help):

1. MsgBox saying "Your choice means ..." and in place of ... I would like the value in a particular cell.

2. Then I would like Yes and No options to appear below the above text in the message box. If you click No then the message box disappears and you carry on. If you clicked Yes then another message box would appear with a message "xyz". At this point, your screen gets locked unless a password is entered.

Sorry I am asking for too much here but I would appreciate your help (just to assure you that this will help with a research tool I am working on).

I tried the following but (because my skills are rubbish) failed miserably:

Sub Done()
M1 = "Your choice means "
M2 = M1 & ActiveSheet.Range("D5")
MsgBox M2
End Sub

Thanks
ExcelStuff
 
Not completely sure what you meant by "lock screen", but this macro will keep you in a loop until password (currently set as "MyPassword" is entered).
Code: 
Sub Done()
Dim cValue As String
Dim uChoice As VbMsgBoxResult
Const pWord = "MyPassword"

'Retrieve cell value
cValue = ActiveSheet.Range("D5").Value
'Determine user's first choice
uChoice = MsgBox("Your choice means: " & cValue, vbYesNo, "Choice")

If uChoice <> vbNo Then
    'Stay in a loop until password is entered
    Do Until InputBox("Please enter password", "XYZ", "****") = pWord
    Loop
   
End If


End Sub
 
Thanks, Somendra and Luke. Elegant solutions and spot on. Very very helpful indeed!

Now, I suppose a couple of minor thing:

1. How do I insert a line break in the message? I would like to add a next line in the same message box saying "Are you sure?"

2. How do you "beautify" the message, like font size, bold, box color etc?

Sincere thanks!
ExcelStuff
 
excel_stuff said:
Thanks, Somendra and Luke. Elegant solutions and spot on. Very very helpful indeed!

Now, I suppose a couple of minor thing:

1. How do I insert a line break in the message? I would like to add a next line in the same message box saying "Are you sure?"

2. How do you "beautify" the message, like font size, bold, box color etc?

Sincere thanks!
ExcelStuff
Click to expand...


Code: 
1.Msgbox "Line1" & vbcrlf & "Line2" & vbnewline & "Line3"
2. That's not allowed!!!
 
@excel_stuff

Another option for inserting an extra line is to use function CHR(10) in concatenation with the function.

message_bog = MsgBox("Your Choice means " & Chr(10) & Sheet1.Cells(2, 1).Value, vbYesNo)

Regards,
 
If you want to start changing the font color/size, it can done with a UserForm which gives you much more options, but you also have to build everything yourself and is probably beyond the scope of this current task in terms of complexity.
 
You must log in or register to reply here.
Back
Top