User row selection input

[rjplante]

I have a macro started that opens a separate workbook and directs the user to the correct page. I want the macro to display a message box to tell the user to select a row, by clicking on the line number in column A, and then clicking "OK" on my message box. This means I need the user to be able to access the scroll bar and the worksheet behind the message/input box.

Once a row has been selected I have the remainder of the macro written to copy the correct cells over to my main worksheet.

I just do not know how to write the message/input box that would allow for user interaction and then complete my macro. I have posted what I have below.

Thanks for the help.

Robert

Sub Data_transfer()

Application.ScreenUpdating = False

File1 = ActiveWorkbook.Name

ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
Workbooks.Open Filename:= _
    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data\Master Cryobag Testing Log.xlsm"

Sheets("Main Log Data").Activate
    
'Select the correct row
I = MsgBox("Select the line number of the row that contains the " & vbCrLf & "testing results for the cryobag used by lot# " & Sheets("Intro Page").Range("T21").Value & vbCrLf & "Click ""OK"" When the line number has been selected.", vbOkayonly + vbExclamation)

'**** AFTER SELECTION HAS BEN MADE *****

ActiveSheet.Unprotect Password:="bioe1025"

Range("AA5").Value = ActiveCell.Value

Workbooks(File1).Sheets("Cryobag Testing 
Summary").Range("AA5:AE5").Value = Workbooks("Master Cryobag Testing Log.xlsm").Sheets("Main Log Data").Range("AA5:AE5").Value

Range("AA5").ClearContents

ActiveSheet.Protect Password:="bioe1025"

ActiveWorkbook.Save  
ActiveWorkbook.Close 

Workbooks(File1).Activate

Application.ScreenUpdating = True

End Sub

 

[VoG]

You could do something like this

Dim r As Range
Set r = Application.InputBox("click in the cell to copy", Type:=8)
r.Copy

 

[sykes]

How about either:
1. If the user can SEE the appropriate row, use an inputbox instead, and the input box message can tell them to "...type in the row number..."

or

2. Use a userform, displayed modelessly with a label for their instruction, an "OK" button, and a TextBox for them to type the row number into.
Displaying the form modelessly means they'll be able to scroll down the worksheet to see the correct row.

 

[rjplante]

I have pasted the code in below and tested it in another workbook with just a simple message.

When I implemented it as listed below, I get a "Subscript out of range" error.

What is wrong?

Dim r As Range

Set r = Application.InputBox("Select the line number of the row that contains the " & vbCrLf & "testing results for the cryobag used by lot# " & Sheets("Intro Page").Range("T21").Value & "." & vbCrLf & "Click ""OK"" When the line number has been selected.", Type:=8 + vbOKOnly)

Range("AA5").Value = r.Value

 

[VoG]

Which line of code is highlighted when you click Debug?

 

[rjplante]

The middle row with the "Set r = ..." I have a block of code above the "Dim r as Range" line and I wonder if that has to be at the very top before any other code is executed?

Thanks,

Robert

 

[VoG]

There is no OKOnly available with that command. Try

Set r = Application.InputBox("Select the line number of the row that contains the " & vbCrLf & "testing results for the cryobag used by lot# " & Sheets("Intro Page").Range("T21").Value & "." & vbCrLf & "Click ""OK"" When the line number has been selected.", Type:=8)

 

[rjplante]

Same Run time error 9, Subscript out of range issue.

I even moved "Dim r..." statement to top of macro and still no luck.

Code now appears as below.

Set r = Application.InputBox("Select the line number of the row that contains the " & vbCrLf & "testing results for the cryobag used by lot# " & Sheets("Intro Page").Range("T21").Value & "." & vbCrLf & "Click ""OK"" When the line number has been selected.", Type:=8)

Range("AA5").Value = r.Value

 

[VoG]

Subscript 9 means that something doesn't exist. Probably the sheet "Intro Page" is misspelt.

 

[rjplante]

Thanks. I had to add the workbooks("XX"). command to the front of the sheets line.

You're the best.

Robert