UserForm or MsgBox to select from a list?

[DK]

Using Excel 2007;
I have a list of sample records on Sheet3 in a range called "Samples"
(B4:Z14).
I want a macro to; Request the user to select one sample, then fill in
certain named cells on a report using the selected sample record. Here is
some code that fills one of the cells. This part is working fine.

Dim sampleno As Integer
sampleno = 1
Range("mfg") = Sheet3.Range("Samples").Cells(sampleno, 4)

How can I best have the user select one sample to supply the variable
"sampleno"?
The samples are numbered 1-10. I'm not familiar with UserForms but would
give it a try if that's the way to go.
If there is a better method, I'd be glad to see it.
Thanks much.

DK

 

[Bernie Deitrick]

DK,

Perhaps?

sampleno = Application.InputBox("Choose a sample number between 1 and 10",
Type:=1)

HTH,
Bernie
MS Excel MVP

 

[DK]

Bernie,
Thank you for the tip. That worked nicely.
I added an if statement to handle the 'Cancel'.
If sampleno = 0 Then
Exit Sub
This seems to work ok. Is that the appropriate method of handling Cancel?
DK