Solved

Excel 2010 Open Workbook via VBA

Posted on 2014-11-11
4
112 Views
Last Modified: 2014-11-11
I have a very simple form created in excel 2010. On the form in cell E43 I have a conditional drop down box with a yes or no choice. I have a macro with the following code.

If Worksheets("Sheet1").Range("e43").Value = "Yes" Then
Workbooks.Open Filename:="S:\Test\Information\Form.xls    
End If
End Sub

This works if I select yes in the drop down but in order to get my form.xls to open I have to tab off of cell e43. How can I get the form.xls to open immediately one time upon selecting yes?
0
Comment
Question by:tmaususer
  • 2
4 Comments
 
LVL 10

Assisted Solution

by:Anthony Berenguel
Anthony Berenguel earned 250 total points
ID: 40436051
Try putting your code in the worksheet_change event of the worksheet.
0
 

Author Comment

by:tmaususer
ID: 40436083
Ah. That simple. Ok one more add on. My worksheet opens fine but minimizes to my task bar. Any way to open that up maximized and have the user decide to minimize?
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40436107
Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E43")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Target = "Yes" Then
Workbooks.Open Filename:="S:\Test\Information\Form.xls"
End If
End Sub

Open in new window

0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 250 total points
ID: 40436112
Try this code then:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E43")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Target = "Yes" Then
Application.ScreenUpdating = False
Workbooks.Open Filename:="S:\Test\Information\Form.xls"
   ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = True
End If
End Sub
    

Open in new window

0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Random times with 12/24 hour switching 9 28
V-Lookup 11 20
collaboration tool but not sharpoint 2 11
Using SUMPRODUCT with a Date Range 6 21
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

828 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