Solved

Excel 2010 Open Workbook via VBA

Posted on 2014-11-11
4
119 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
[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
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

732 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