webbrowser object causes crash on sheet change

A

Atishoo

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.Object.Navigate2 _
"http://1.12.1.openform"
Else
Sheets("Main
Board").Shapes("WebBrowser2").OLEFormat.Object.Object.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.Object.Navigate2 _
"http://1.12.1.openform"
Else
Sheets("Main
Board").Shapes("WebBrowser2").OLEFormat.Object.Object.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.Object.Navigate2 _
"http://1.12.1.openform"
Else
Sheets("Main
Board").Shapes("WebBrowser2").OLEFormat.Object.Object.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.Object.Navigate2 _
"http://1.12.1.openform"
Else
Sheets("Main
Board").Shapes("WebBrowser2").OLEFormat.Object.Object.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.Object.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
 

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

slow code execution 0
union range 5
Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
Word macro that can call cells out of Excel for a find and replace 0
Change_Event troubles, no error & no work. 6
Date & Time Picker Control 6.0 - another challenge (for me) 4
Match style 0
If cancel list box don't enter FALSE in list 2

Top