User row selection input

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
574
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You could do something like this

Code:
Dim r As Range
Set r = Application.InputBox("click in the cell to copy", Type:=8)
r.Copy
 
Upvote 0
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.
 
Upvote 0
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?

Code:
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
 
Upvote 0
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
 
Upvote 0
There is no OKOnly available with that command. Try

Code:
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)
 
Upvote 0
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.

Code:
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
 
Upvote 0
Subscript 9 means that something doesn't exist. Probably the sheet "Intro Page" is misspelt.
 
Upvote 0
Thanks. I had to add the workbooks("XX"). command to the front of the sheets line.

You're the best.

Robert
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top