Solved

Excel 2010 Open Workbook via VBA

Posted on 2014-11-11
4
104 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
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 simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now