maximize one of three minimized workbooks to certain sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. Not sure if this can be done. At one point in my routing, I have three
workbooks that are minimized. I need to maximize one of the three and have
to open on sheet two. Anyone have any ideas on how to code this? Thanks in
advance.
 
Thanks for the response. I thought about that but the problem is the
workbook used by many people who will save it with a different name. (Its a
budget tool.) Would a wildcard work for a file name? I could probably tell
everyone just to enter their initials or something after the workbook name.
 
SLP,

Okay>do you have code that minimizes the workbooks? If so you can set a
variable to keep the workbook name.
dim Oldbook as string
Oldbook=activeworkbook.name
 
Tried your suggestion and I can't make it work. I placed the variable at the
beginning of the code for that command button that starts this part of my
routine and then added the dim Oldbook as string at the beginning of the code
that minimizes the other workbook. Any other ideas? I'll keep at it....just
in case I need to add the variable someplace else.
 
SLP,

make it a Public variable.
set it equal to the workbook that you are trying to call up. I assume it is
the workbook the macro button is located. Does your code control what
workbook is getting used and then it will return to the one with the button?
 
I'll try that. The workbook that has the macro button knows which workbook
is getting used. Don't think I ever made a public variable but will try.
Will keep you posted.
 
Well, I guess I am just stupid. I can't get it to work. The budget workbook
has a button that opens another workbook (book 2) that has input boxes for
the user to select center and location for a filter, that data being placed
into book 3. Book 2 gets minimized. I want book 3 to be minimized and the
budget workbook to be maximized with sheet 2 being activated.
 
Thank you. This is the start of the button. We are in the budget workbook
and it calls up a workbook called link.

Dim Oldbook As String
Oldbook = ActiveWorkbook.Name

Windows("Link.xls").Activate
'
Range("A1").Select
ActiveCell.FormulaR1C1 = "CC"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Location"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "CC"
Range("AH1").Select
ActiveCell.FormulaR1C1 = "Location"
Range("AH2").Select
ActiveCell.FormulaR1C1 = "0"
Range("AH2").Select
Selection.NumberFormat = "0"

This is the end of the button. The Liink workbook has had a user defined
filter set and now gets saved with a different name.

Sub SaveOneFile()
'
ActiveWorkbook.SaveAs Filename:="H:\BITemp.xls", FileFormat:=xlExcel4, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


ActiveWindow.WindowState = xlMinimized
Windows("Oldbook.xls").Activate
ActiveWindow.WindowState = xlMaximized

Sheets("Buttons").Select
 
SLP,

Here is some code that wil create 4 workbooks>save them to c:\SLP and then
activate Book2.xls and select sheet2.
Maybe this help you.

Sub SLP()
Dim i As Integer
Dim NewWkbk As Workbook
Dim Oldbook As String

'this works if you are using excel 2003 change the file extension to .xlsx
if using 2007

For i = 1 To 4
Set NewWkbk = Workbooks.Add
ActiveWorkbook.SaveAs "C:\SLP\Book" & i
If ActiveWorkbook.Name = "Book3.xls" Then
Oldbook = ActiveWorkbook.Name
End If
Next i

Windows(Oldbook).Activate
Sheets("sheet2").Select

End Sub
 
SLP,

Put this statement at the top of your module below the "Option Explict"

Public Oldbook as string

Delete the Oldbook definition in your button code "Dim Oldbook as String"
Also change the code:
Windows("Oldbook.xls").Activate
To
Windows(Oldbook).Activate

That should do it, however, I don't see how/where the call is made for
"SaveOneFile" .
 
Thanks, that almost does the trick. Sheet 2 (which is called Buttons)
doesn't activate. The SaveOneFile gets called from another sub which I
didn't send to you.

Your next response was out of synch with what I am doing but may come in
handy in a future project.
 
SLP,

I was sending you an example of multiple files and calling a workbook and
select a sheet. What sheet is selected when you return to the original book?
Also you do not need to make a Maximize call to the workbook. Just
acitivating the book will bring it up maximized.
 
Oh, OK, thank you. The third sheet (ComparisonAndModify) is active. It is
the sheet where the BTITemp workbook (workbook 3) puts its data. I need my
end users back on the second sheet (Buttons) as they have two more steps to
do at this point.
 
SLP,

The code to call the sheet is correct. Try launching the macro recorder and
then select different sheets and make sure one of them is Buttons. Then look
at the code and verify the sheet name.
 
Did that and the sheet name is correct. It still won't activate the Buttons
sheet. BTW, I really appreciate all the help.
 
SLP,

What cell is active? Try putting this code right before the call to the
sheet button>
Application.CutCopyMode = False
 
The range that gets pasted into the budget workbook is highlighted. I added
that code as the first line in the SaveAsOne sub and same results. Oh, let
me try to add it to the sub that starts the button. Maybe that is what you
meant.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Maximize and Protect Worksheet 1
Maximized Workbook 4
Workbook Window will not maximize 1
Excel VBA - How to maximize a minimized workbook - the workbook nameis dynamic 4
Minimize userform 3
Wierd - when saving wb & protected...it opens with sheet 'down' 5
Combine three InputBoxes into one? 7
Is there a fancy way to open a workbook in a minimized window stat 2

Back
Top