Solved

Excel 2010 Open Workbook via VBA

Posted on 2014-11-11
4
98 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
Comment Utility
Try putting your code in the worksheet_change event of the worksheet.
0
 

Author Comment

by:tmaususer
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

771 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

7 Experts available now in Live!

Get 1:1 Help Now