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

DK,

Perhaps?

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

HTH,
Bernie
MS Excel MVP



"DK" wrote in message
...
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,
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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
DK,

Perhaps?

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

HTH,
Bernie
MS Excel MVP



"DK" wrote in message
...
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

It is an OK way, since you dimmed sampleno as an integer, Excel casts the
False return that cancel generates into an integer, which translates as 0.
A more formal way would be to dim sampleno as a variant, and then it would
be False.... I would stick with your way.... VBA is definitely an "if it
ain't broke" kind of language.

Bernie


"DK" wrote in message
...
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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
DK,

Perhaps?

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

HTH,
Bernie
MS Excel MVP



"DK" wrote in message
...
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

Thanks very much for the insite.
DK

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
It is an OK way, since you dimmed sampleno as an integer, Excel casts the
False return that cancel generates into an integer, which translates as 0.
A more formal way would be to dim sampleno as a variant, and then it would
be False.... I would stick with your way.... VBA is definitely an "if it
ain't broke" kind of language.

Bernie


"DK" wrote in message
...
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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
DK,

Perhaps?

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

HTH,
Bernie
MS Excel MVP



"DK" wrote in message
...
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