Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
webbrowser object causes crash on sheet change
I have a large sub that controls a web browser address dependent on the
active cell! this is triggered by a selection change event. if this has been trigered prior to a sheet change excell crashes as soon as the sheet change takes place if it hasnt been trigered then it doesnt. The web browser is placed on the main page not on a user form, if I make this not visible through the proporties menu it doesnt crash but if I select visible = false as a command prior to sheet change it still crashes! Im using 2003 on XP have installed SP3. Its a pretty big sub but it works brilliantly just so long as you dont want to change sheet!! The upper half of the sub controlls the web browser address and the lower part triggers user forms, but its definately the web browser that is causing the crash! Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If ActiveSheet.Name = "Main Board" Then With Worksheets("Main Board") With WebBrowser2 If Not Intersect(ActiveCell, Range("ULHS")) Is Nothing Then If Cells(ActiveCell.Row, "ar").Value = "" Then Sheets("Main Board").Shapes("WebBrowser2").OLEFormat.Object.Obj ect.Navigate2 _ "http://1.12.1.openform" Else Sheets("Main Board").Shapes("WebBrowser2").OLEFormat.Object.Obj ect.Navigate2 _ Cells(ActiveCell.Row, "ar").Value End If End If If Not Intersect(ActiveCell, Range("LLHS")) Is Nothing Then If Cells(ActiveCell.Row, "ar").Value = "" Then Sheets("Main Board").Shapes("WebBrowser2").OLEFormat.Object.Obj ect.Navigate2 _ "http://1.12.1.openform" Else Sheets("Main Board").Shapes("WebBrowser2").OLEFormat.Object.Obj ect.Navigate2 _ Cells(ActiveCell.Row, "ar").Value End If End If If Not Intersect(ActiveCell, Range("URHS")) Is Nothing Then If Cells(ActiveCell.Row, "av").Value = "" Then Sheets("Main Board").Shapes("WebBrowser2").OLEFormat.Object.Obj ect.Navigate2 _ "http://1.12.1.openform" Else Sheets("Main Board").Shapes("WebBrowser2").OLEFormat.Object.Obj ect.Navigate2 _ Cells(ActiveCell.Row, "av").Value End If End If If Not Intersect(ActiveCell, Range("LRHS")) Is Nothing Then If Cells(ActiveCell.Row, "av").Value = "" Then Sheets("Main Board").Shapes("WebBrowser2").OLEFormat.Object.Obj ect.Navigate2 _ "http://1.12.1.openform" Else Sheets("Main Board").Shapes("WebBrowser2").OLEFormat.Object.Obj ect.Navigate2 _ Cells(ActiveCell.Row, "av").Value End If End If If Not Intersect(ActiveCell, Range("dutyrange")) Is Nothing Then Sheets("Main Board").Shapes("WebBrowser2").OLEFormat.Object.Obj ect.Navigate2 _ "https://wmail/public" End If End With End With Dim BigSUName As Range With UserForm9 Set BigSUName = Application.Union( _ Range("sunamel"), _ Range("sunamer")) If Not Intersect(Target, BigSUName) Is Nothing Then UserForm9.TextBox1.Value = ActiveCell.Value UserForm9.Show End If End With Dim BigSupportWorker As Range With ComboBox1 Set BigSupportWorker = Application.Union( _ Range("supportworkersl"), _ Range("supportworkersr")) If Not Intersect(Target, BigSupportWorker) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With Dim BigCpaRange As Range With UserForm5 Set BigCpaRange = Application.Union( _ Range("cpadatel"), _ Range("cpadater")) If Not Intersect(Target, BigCpaRange) Is Nothing Then UserForm5.Show End If End With Dim BigPhysicalRange As Range With UserForm6 Set BigPhysicalRange = Application.Union( _ Range("physicaldatel"), _ Range("physicaldater")) If Not Intersect(Target, BigPhysicalRange) Is Nothing Then UserForm6.Show End If End With Dim BigCarerRange As Range With UserForm4 Set BigCarerRange = Application.Union( _ Range("carerrange1"), _ Range("carerrange2")) If Not Intersect(Target, BigCarerRange) Is Nothing Then UserForm4.Calendar1.Visible = False UserForm4.ComboBox1.Enabled = False UserForm4.Show End If End With Dim BigBloodRange As Range With UserForm1 Set BigBloodRange = Application.Union( _ Range("bloodrange1"), _ Range("bloodrange2")) If Not Intersect(Target, BigBloodRange) Is Nothing Then UserForm3.Show End If End With Dim BigDepotRange As Range With UserForm1 Set BigDepotRange = Application.Union( _ Range("depotrange1"), _ Range("depotrange2")) If Not Intersect(Target, BigDepotRange) Is Nothing Then UserForm2.Show End If End With Dim BigMedsDateRange As Range With UserForm1 Set BigMedsDateRange = Application.Union( _ Range("medsdate1"), _ Range("medsdate2")) If Not Intersect(Target, BigMedsDateRange) Is Nothing Then UserForm1.Calendar1.Visible = False UserForm1.ComboBox1.Enabled = False UserForm1.CheckBox1.Enabled = False UserForm1.CheckBox2.Enabled = False UserForm1.CheckBox3.Enabled = False UserForm1.CheckBox4.Enabled = False UserForm1.CheckBox5.Enabled = False UserForm1.CheckBox6.Enabled = False UserForm1.Show End If End With Dim BigTimeRange As Range With ActiveSheet.ListBox1 Set BigTimeRange = Application.Union( _ Range("time1"), _ Range("time2"), _ Range("time3"), _ Range("time4"), _ Range("time5"), _ Range("time6"), _ Range("time7"), _ Range("time8"), _ Range("time9"), _ Range("time10"), _ Range("time11"), _ Range("time12")) If Not Intersect(Target, BigTimeRange) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With Dim BigWardRange As Range With ActiveSheet.ComboBox67 Set BigWardRange = Application.Union( _ Range("wards1"), _ Range("wards2")) If Not Intersect(Target, BigWardRange) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With Dim BigTeamRange As Range With ActiveSheet.ComboBox68 Set BigTeamRange = Application.Union( _ Range("teams1"), _ Range("teams2")) If Not Intersect(Target, BigTeamRange) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With With UserForm7 If Not Intersect(Target, Range("date9")) Is Nothing Then UserForm7.Show End If End With With UserForm8 If Not Intersect(Target, Range("eventdescriptionrange")) Is Nothing Then UserForm8.Show End If End With With ActiveSheet.ComboBox53 If Not Intersect(Target, Range("dutyrange")) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With Dim BigAlertRange As Range With ActiveSheet.ComboBox56 Set BigAlertRange = Application.Union( _ Range("alertrange"), _ Range("alertslhs")) If Not Intersect(Target, BigAlertRange) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With Dim BigWorkersMon As Range With ActiveSheet.ComboBox54 Set BigWorkersMon = Application.Union( _ Range("workersmonl"), _ Range("workersmonr")) If Not Intersect(Target, BigWorkersMon) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With Dim BigWorkersTue As Range With ActiveSheet.ComboBox57 Set BigWorkersTue = Application.Union( _ Range("workerstuel"), _ Range("workerstuer")) If Not Intersect(Target, BigWorkersTue) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With Dim BigWorkersWed As Range With ActiveSheet.ComboBox58 Set BigWorkersWed = Application.Union( _ Range("workerswedl"), _ Range("workerswedr")) If Not Intersect(Target, BigWorkersWed) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With Dim BigWorkersThur As Range With ActiveSheet.ComboBox59 Set BigWorkersThur = Application.Union( _ Range("workersthurl"), _ Range("workersthurr")) If Not Intersect(Target, BigWorkersThur) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With Dim BigWorkersFri As Range With ActiveSheet.ComboBox60 Set BigWorkersFri = Application.Union( _ Range("workersfril"), _ Range("workersfrir")) If Not Intersect(Target, BigWorkersFri) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With Dim BigWorkersSat As Range With ActiveSheet.ComboBox61 Set BigWorkersSat = Application.Union( _ Range("workersatl"), _ Range("workerssatr")) If Not Intersect(Target, BigWorkersSat) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With With ActiveSheet.ComboBox55 If Not Intersect(Target, Range("montaskslhs")) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With With ActiveSheet.ComboBox62 If Not Intersect(Target, Range("tuetaskslhs")) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With With ActiveSheet.ComboBox63 If Not Intersect(Target, Range("wedtaskslhs")) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With With ActiveSheet.ComboBox64 If Not Intersect(Target, Range("thurtaskslhs")) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With With ActiveSheet.ComboBox65 If Not Intersect(Target, Range("fritaskslhs")) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With With ActiveSheet.ComboBox66 If Not Intersect(Target, Range("sattaskslhs")) Is Nothing Then .Visible = True .Top = Target.Top + Target.Cells.Height .Left = Target.Left Else .Visible = False End If End With End If Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print the WebBrowser Object | Excel Programming | |||
re-direct hyperlink event to WebBrowser Object in Excel | Excel Programming | |||
Excel crash closing ADO connection object | Excel Programming | |||
Adding a webBrowser object dynamically to a sheet | Excel Programming |