Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.Activate / Window.Activate problem
OK. I give up. Someone smarter than me is going to need to tell me
why I am deleting the worksheet from the wrong workbook. Seems like I can't figure out how to correctly shift from one to another. The last statement here gives me a 'subscript out of range' error, and I can't figure out why. Code follows: Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range) Dim cell As Range Dim wrksht As Worksheet Dim SendMail As Outlook.MailItem Dim i%, ReportPage$, ReportRow%, Subject$, SelectedAddresses$, ScheduleName$, IndividualName$ If sh.Name Like "####Q#" Then If Target.Row <= 2 And Cells(Target.Row, Target.Column).Value < "" Then If MsgBox("Generate report for " & Range(sh.Name & "!" & Cells(1, Target.Column).Address).Value & " " & Range(sh.Name & "!" & Cells(2, Target.Column).Address).Value & "?", vbYesNo) = vbYes Then ScheduleName$ = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) IndividualName$ = ScheduleName$ & " " & Range(sh.Name & "!" & Cells(1, Target.Column).Address).Value & " " & Range(sh.Name & "!" & Cells(2, Target.Column).Address).Value Workbooks.Add ActiveWorkbook.SaveAs Filename:=IndividualName$ Workbooks(ScheduleName$ & ".xls").Activate Sheets(Array(Left(ActiveSheet.Name, 5) & "1", Left(ActiveSheet.Name, 5) & "2", Left(ActiveSheet.Name, 5) & "3", Left(ActiveSheet.Name, 5) & "4")).Copy befo=Workbooks(IndividualName$ & ".xls").Sheets(1) Workbooks(IndividualName$ & ".xls").Activate ' Windows("CPF Time Off Schedule V2 Mia Bijaksana.xls").Activate 'Sheets("Sheet1").Delete Workbooks(ScheduleName$ & ".xls").Activate Windows(ScheduleName$ & ".xls").Activate Workbooks(IndividualName$ & ".xls").Activate Windows(IndividualName$ & ".xls").Activate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.Activate / Window.Activate problem
First, I'm kind of surprised you use the _sheetselectionchange event. But
that's your choice. But the nice thing about that is that there are things passed to that routine--the sheet (as sh) and the range (as target). And since you're in the ThisWorkbook module, you can use the Me. keyword to refer to the workbook with the code. And by using those variables, you can kind of slim down your code. I _think_ that this does the same as you had before--but double check it. Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range) Dim IndividualName As String Dim NewWkbk As Workbook Dim shPrefix As String shPrefix = Left(sh.Name, 5) If UCase(sh.Name) Like "####Q#" Then If Target.Row <= 2 And Target.Value < "" Then If MsgBox("Generate report for " _ & sh.Name & "!" & sh.Cells(1, Target.Column).Value & " " _ & sh.Name & "!" & sh.Cells(2, Target.Column).Value _ & "?", vbYesNo) = vbYes Then Set NewWkbk = Workbooks.Add(1) 'single sheet in the newworkbook NewWkbk.Worksheets(1).Name = "Dummy" 'delete it later Me.Sheets(Array(shPrefix & "1", shPrefix & "2", _ shPrefix & "3", shPrefix & "4")).Copy _ befo=NewWkbk.Sheets(1) Application.DisplayAlerts = False NewWkbk.Worksheets("dummy").Delete Application.DisplayAlerts = True IndividualName _ = Left(Me.FullName, Len(Me.FullName) - 4) _ & " " & sh.Cells(1, Target.Column).Value & " " _ & sh.Cells(2, Target.Column).Value NewWkbk.SaveAs Filename:=IndividualName 'me.activate 'or stay in the new workbook? End If End If End If End Sub ===== One of the problems with your: Sheets("Sheet1").Delete is that it's unqualified. You didn't tell it what workbook Sheet1 belonged to. In a general module, that "sheets("sheet1")" would refer to the activeworkbook. But behind ThisWorkbook, excel figures anything unqualified belongs to the thing that owns that module--in this case the workbook with the code. And since you didn't have a worksheet named sheet1 in that workbook--you heard the big kaboooom! Tim wrote: OK. I give up. Someone smarter than me is going to need to tell me why I am deleting the worksheet from the wrong workbook. Seems like I can't figure out how to correctly shift from one to another. The last statement here gives me a 'subscript out of range' error, and I can't figure out why. Code follows: Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range) Dim cell As Range Dim wrksht As Worksheet Dim SendMail As Outlook.MailItem Dim i%, ReportPage$, ReportRow%, Subject$, SelectedAddresses$, ScheduleName$, IndividualName$ If sh.Name Like "####Q#" Then If Target.Row <= 2 And Cells(Target.Row, Target.Column).Value < "" Then If MsgBox("Generate report for " & Range(sh.Name & "!" & Cells(1, Target.Column).Address).Value & " " & Range(sh.Name & "!" & Cells(2, Target.Column).Address).Value & "?", vbYesNo) = vbYes Then ScheduleName$ = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) IndividualName$ = ScheduleName$ & " " & Range(sh.Name & "!" & Cells(1, Target.Column).Address).Value & " " & Range(sh.Name & "!" & Cells(2, Target.Column).Address).Value Workbooks.Add ActiveWorkbook.SaveAs Filename:=IndividualName$ Workbooks(ScheduleName$ & ".xls").Activate Sheets(Array(Left(ActiveSheet.Name, 5) & "1", Left(ActiveSheet.Name, 5) & "2", Left(ActiveSheet.Name, 5) & "3", Left(ActiveSheet.Name, 5) & "4")).Copy befo=Workbooks(IndividualName$ & ".xls").Sheets(1) Workbooks(IndividualName$ & ".xls").Activate ' Windows("CPF Time Off Schedule V2 Mia Bijaksana.xls").Activate 'Sheets("Sheet1").Delete Workbooks(ScheduleName$ & ".xls").Activate Windows(ScheduleName$ & ".xls").Activate Workbooks(IndividualName$ & ".xls").Activate Windows(IndividualName$ & ".xls").Activate -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.Activate / Window.Activate problem
Thanks Dave! Very helpful. Have never understood or used 'me' before,
and now I understand the need for qualification in this project. There was one innocent looking thing you changed: Cells(Target.Row, Target.Column).Value to: Target.Value Seems reasonable, and works when target.value = "", but crashes when target.value = "Someones Name" with a type mismatch. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.Activate / Window.Activate problem
I'm guessing that it wasn't the change that caused the trouble. I'm guessing
that it was because you selected more than one cell. If that's the case, you could tell the code to just drop out if the user selects more than one cell: Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, _ ByVal Target As Range) Dim IndividualName As String Dim NewWkbk As Workbook Dim shPrefix As String if target.cells.count 1 then exit sub shPrefix = Left(sh.Name, 5) 'rest of code here.... ========= Or you could just look at the first cell in that selection. If Target.Row <= 2 And Target.cells(1,1).Value < "" Then ========= Post back if I guessed wrong... Tim wrote: Thanks Dave! Very helpful. Have never understood or used 'me' before, and now I understand the need for qualification in this project. There was one innocent looking thing you changed: Cells(Target.Row, Target.Column).Value to: Target.Value Seems reasonable, and works when target.value = "", but crashes when target.value = "Someones Name" with a type mismatch. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generic Window Activate | Excel Programming | |||
Activate new window | Excel Programming | |||
Activate new window | Excel Programming | |||
Activate Non-Excel Window Through VBA | Excel Programming | |||
How to keep window + form activate together | Excel Programming |