TheBaroness
asked on
If data is entered in a cell, I would like to automatically open a worksheet
When a user enters data in a cell of one specific column of a worksheet, I would like to open another worksheet in the workbook upon exiting that cell. So if the user enters a name in cell C6, worksheet MG Mentions will open.
If the user types a path in the cell then it is possible. Else you will have to hard code the path in the code.
Sid
Sid
What Version of Microsoft office are you using?
If you right Click on the the cell and click HyperLink and chose a excel document that has been previously saved on computer.
If you right Click on the the cell and click HyperLink and chose a excel document that has been previously saved on computer.
Here, its enhanced - I created a sheet to store the filename which gets opened when change happens in column C.
Note this will open the file even if a cell is being cleared...
See code and attached
Dave
Open-File-Col-C-change-r2.xls
Note this will open the file even if a cell is being cleared...
See code and attached
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Not Intersect(Target, Range("C1").EntireColumn) Is Nothing Then
Workbooks.Open ([fname])
End If
End Sub
Dave
Open-File-Col-C-change-r2.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's exactly what I wanted! Everyone else was under the impression I wanted to open another workbook, but this is the solution I was looking for. Many thanks!
Well, when you say "open another" - we kindof tend to get that impression. As oppose to "add a new".
funny!
Cheers,
dave
funny!
Cheers,
dave
ASKER
Actually, I said "open another worksheet in the workbook" which should have clarified what I intended. Additionally, my example specified opening a worksheet, not a workbook. I'm not sure how much clearer I could have made it :)
You gave no example in your original question. I was just suggesting a reason why "everyone else" potentially got that impression.
I don't usually read "open a worksheet" to mean add a new worksheet to an existing workbook. In prior versions of excel, there was only the worksheet and no tabs either. So, "open a worksheet" was literally opening a worksheet file.
But you did just fine and one of us deciphered what you needed.
I'm glad you got your solution.
Dave
I don't usually read "open a worksheet" to mean add a new worksheet to an existing workbook. In prior versions of excel, there was only the worksheet and no tabs either. So, "open a worksheet" was literally opening a worksheet file.
But you did just fine and one of us deciphered what you needed.
I'm glad you got your solution.
Dave
TheBaroness,
I thought your question was pretty clear. You mentioned worksheet three times between the question title and body, and even named it :)
That said, the verb "open" is generally applied to workbooks that had previously been closed and now need to be opened. That's why Sid, Dave and bootheelbank made the suggestions they did. The correct verb for what you wanted to do is "activate," but this distinction is primarily enforced by VBA code syntax. I wouldn't expect most people to know that fine point.
Thanks for the kind words and grade!
Brad
I thought your question was pretty clear. You mentioned worksheet three times between the question title and body, and even named it :)
That said, the verb "open" is generally applied to workbooks that had previously been closed and now need to be opened. That's why Sid, Dave and bootheelbank made the suggestions they did. The correct verb for what you wanted to do is "activate," but this distinction is primarily enforced by VBA code syntax. I wouldn't expect most people to know that fine point.
Thanks for the kind words and grade!
Brad
Open your Debugger and insert this code into the SHEET CODEPAGE you desire (e.g., Sheet1, etc.)
Open in new window
Dave