Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

If data is entered in a cell, I would like to automatically open a worksheet

Posted on 2011-03-07
10
Medium Priority
?
351 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:TheBaroness
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35062043
In Sheet1, you can create an event based on cell changes.  This one, below, looks at any changes in Column C, then opens the file - you'll need to put the fill path in the code.

Open your Debugger and insert this code into the SHEET CODEPAGE you desire (e.g., Sheet1, etc.)
 
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("C1").EntireColumn) Is Nothing Then
        Workbooks.Open ("MG Mentions")
    End If
End Sub

Open in new window

Dave
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35062045
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
0
 

Expert Comment

by:bootheelbank
ID: 35062106
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.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 35062112
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
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

Open in new window


Dave
Open-File-Col-C-change-r2.xls
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 35063013
The macro to jump to another worksheet must be stored in the worksheet code pane. Assuming that you want to jump to another worksheet in the same workbook, you do so by Activating that worksheet rather than by opening a workbook.

The following macro watches column C, starting in cell C2. If the user enters any value in those cells, then the macro jumps to the MG Mentions worksheet. If the user clears a value, then no jump is performed.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
Set targ = Range("C2")  'First cell to watch
Set targ = Range(targ, Cells(Rows.Count, targ.Column)) 'All the cells to bottom of worksheet
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
    If targ.Cells(1, 1) <> "" Then Worksheets("MG Mentions").Activate
End If
End Sub

Open in new window

Since you are using Excel 2007, you must save the workbook as a macro-enabled .xlsm workbook type. If you save it as .xlsx, then the macro will be removed upon saving it.

You will also need to enable macros when the workbook is opened. If you do not, then the code will not work.

Brad
MG-Mentions-Q26869482.xlsm
0
 

Author Closing Comment

by:TheBaroness
ID: 35069830
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!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35069980
Well, when you say "open another" - we kindof tend to get that impression.  As oppose to "add a new".

funny!

Cheers,

dave
0
 

Author Comment

by:TheBaroness
ID: 35070061
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 :)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35070132
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
0
 
LVL 81

Expert Comment

by:byundt
ID: 35070278
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question