open another workbook and select cell

xzaqus

New Member
Joined
Sep 24, 2017
Messages
33
Hello-

I have a workbook with values in column A and the corresponding reference workbook, sheet in column B and C respectively (Col B has workbook name and C has sheet within the workbook).

I need a code such that if a value is missing in Col A, then it should bring up an inputbox, open up the reference workbook, such that the user can select the value from the reference sheet.

This all should be done within the if loop when Col A values are checked.

Help!
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,647
Code:
Sub test()
Dim i As Long
Dim lastrow As Long

lastrow = Range("A1").End(xlDown).Row + 1

For i = 1 To lastrow
    If Range("A" & i).Value = "" Then
        Workbooks(Range("B" & i).Value).Open
        Workbooks(Range("B" & i)).Worksheets(Range("C" & i)).Activate
    End If
Next i
        
End Sub

This might give you a start. I wasn't sure the purpose for the inputbox if you were opening a workbook to find the value that should be in Col A, but I imagine it could be incorporated fairly easily.
 

xzaqus

New Member
Joined
Sep 24, 2017
Messages
33
Thank you for the help in providing a start. However, I need an InputBox so the user can click on the cell in the newly opened workbook and the value from the cell gets filled in the empty cell in the original workbook.

I got the inputbox to show up, but the opened workbook is greyed out. The workbook actually opens up only after the inputbox is canceled. It looks like that excel is not opening another workbook until the sub is active in the first workbook. Any suggestions!


Code:
Sub test()
Dim i As Long
Dim lastrow As Long

lastrow = Range("A1").End(xlDown).Row + 1

For i = 1 To lastrow
    If Range("A" & i).Value = "" Then
        Workbooks(Range("B" & i).Value).Open
        Workbooks(Range("B" & i)).Worksheets(Range("C" & i)).Activate
    End If
Next i
        
End Sub

This might give you a start. I wasn't sure the purpose for the inputbox if you were opening a workbook to find the value that should be in Col A, but I imagine it could be incorporated fairly easily.
 
Last edited:

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,647
That's not really what an inputbox does. Post the code you have so far. Is the "cell in the newly opened workbook" always the same cell? If so, we can just say
Code:
Range("A" & i).Value = WB(Range....).Worksheets(Range....).Range(whatever)

Or at least, is it consistently different? IOW, could a loop with a variable and a certain step rate be used to grab the value of this certain cell in the newly opened workbook?
 

xzaqus

New Member
Joined
Sep 24, 2017
Messages
33
It changes every time. That is why I need to pick up through inputbox
The code I have is

If ActiveSheet.Range(checkCell).Count > 1 Then
Workbooks.Open fileName:=rFilePath

Set checkCell = Application.InputBox(" Select the cell that has the value", "Select cell", Type:=8)


MsgBox checkCell.Address


End if


rFilePath has the path to the file to open. When I run this, an inputbox opens up on top of a greyed out blank new workbook. So, it is of no use. The moment I close the inputbox, the file in rFilePath opens up.





That's not really what an inputbox does. Post the code you have so far. Is the "cell in the newly opened workbook" always the same cell? If so, we can just say
Code:
Range("A" & i).Value = WB(Range....).Worksheets(Range....).Range(whatever)

Or at least, is it consistently different? IOW, could a loop with a variable and a certain step rate be used to grab the value of this certain cell in the newly opened workbook?
 

xzaqus

New Member
Joined
Sep 24, 2017
Messages
33
Correction: The code I have is

If IsEmpty(ActiveSheet.Range(referenceCellAddress)) Then
Workbooks.Open fileName:=rFilePath

Set checkCell = Application.InputBox(" Select the cell that has the value", "Select cell", Type:=8)


MsgBox checkCell.Address


End if


rFilePath has the path to the file to open. When I run this, an inputbox opens up on top of a greyed out blank new workbook. So, it is of no use. The moment I close the inputbox, the file in rFilePath opens up.[/QUOTE]
 

Forum statistics

Threads
1,136,334
Messages
5,675,177
Members
419,552
Latest member
jsanjur

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
Top