Message Box

[Walshy]

I have wrote a basic macro.

What I wanna do is have a box pop up doing the following 3
things:

1 -Saying please enter id (this will enter an id into a
selected cell, type in number then click button ok)

2 -Button saying Dont know id which wil leave the cell
blank

or 3 -Button saying Cancel (this will cancel the macro and
I will assign code to undo the original macro.)

Hope you can help me

Many thanks

 

[Harald Staff]

Hi

Instead of using a cell for entry, try

Sub test()
Dim S As String
S = InputBox("ID please:")
Select Case S
Case "***" 'OK
MsgBox "ID accepted"
Case "" 'Cancelled
Case Else 'wrong
MsgBox "Wrong ID. You're fired.", vbInformation
End Select
End Sub

HTH. best wishes Harald

 

[Guest]

Hi Harold this is great but I want it to return the value
entered in the box onto the selected cell in the
worksheet. also the project number entered could be
anything...

basically what I am doing is I have a table. and when the
user wants to add a new record they click a button and a
macro inserts a row at the first blank copies the formulas
etc from the row above and pastes them into the new row..
The formulas are all vlookups from the first cell so in
the macro I want a box to pop up (as your macro done fine)
and then the user will enter the number into this and then
click ok then this will return the value into the
worksheet and the lookups will all pick up from that. If
they dont know the code the have a button saying that to
leave the row blank or finally cancel which would then
delete the new row and cancel the macro so it goes back to
how it was.

Sorry I should of wrote that originally !!! doh !!! Hope
you can help me... Thankx

PS like the msgbox about your fired !! haha

 

[Harald Staff]

Hi

Instead of
MsgBox "ID accepted"
use
ActiveCell.Value = S

But with all this action and validation, I suggest you o create a userform.
See this tutorial here
http://www.dicks-blog.com/excel/2004/09/linking_userfor.html

HTH. best wishes Harald