Hi I have an excel sheet with a button containing a macro(s). The way that it is accessed by the users means that it opens with internet explorer and I cannot change that. The problem I'm having is that when a second user opens it they cannot use the macro.
This works fine in excel with two users in it but when we do it through explorer it gets a runtime error "Method Sheets of object Workbook failed".
Does anyone know how to prevent this error when using IE?
The button opens a user form with this code -
Clicking "Ok" on the user form runs this code -
Any help greatly appreciated
Thanks
This works fine in excel with two users in it but when we do it through explorer it gets a runtime error "Method Sheets of object Workbook failed".
Does anyone know how to prevent this error when using IE?
The button opens a user form with this code -
Code:
Sub Rectangle25_Click()
Application.ThisWorkbook.Sheets("What's New").Visible = True
Application.ThisWorkbook.Sheets("What's New").Select
UserForm1.Show
End SubClicking "Ok" on the user form runs this code -
Code:
Private Sub CommandButton1_Click()
ThisWorkbook.Activate
Sheets("What's New").Select
Sheets("What's New").Visible = True
Dim strbody As String
Dim bathgate AsString
Dim Pensions As String
Dim Motherwell As String
Dim Kilmarnock As String
Dim Coatbridge As String
Dim Greenock As String
Dim Aberdeen As String
Dim Clydebank As String
Dim GlasgowBC As String
Dim GlasgowBC2 As String
Dim ScotlandBC As String
Dim Dundee As String
Worksheets("What's New").Range("H1").Value = ComboBox1.Value
Worksheets("What's New").Range("I1").Value = ComboBox2.Value
strbody = "Please check the 'What's New' page on the Change Information Hub for updates regarding - " & Sheets("What's New").Range("H1").Value
bathgate = "Bathgate 324"
Motherwell = "Motherwell 1876"
Kilmarnock = "Kilmarnock 876"
Coatbridge = "Coatbridge 5674"
Greenock = "Grennock 9967"
Aberdeen = "Aberdeen 0989"
Clydebank = "Clydebank 8745
GlasgowBC = " 9878 GNBC"
GlasgowBC2 = "0777 GNBC2"
Dundee = "Dundee;2224"
ScotlandBC = "947CCM"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
If Worksheets("What's New").Range("I1").Value = "All" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = bathgate & Motherwell & Kilmarnock & Coatbride & Greenock & Aberdeen & Clydebank & GlasgowBC & GlasgowBC2 & Dundee & ScotlandBC
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Motherwell" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = Motherwell
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Bathgate" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = bathgate
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Kilmarnock" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = Kilmarnock
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Coatbridge" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = Coatbride
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Greenock" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = Greenock
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Aberdeen" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = Aberdeen
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Clydebank" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = Clydebank
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "GlasgowBC" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = GlasgowBC & GlasgowBC2
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Dundee" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = Dundee
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Pension Centres" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = Dundee & Motherwell
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Benefit Centres" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = GlasgowBC & GlasgowBC2 & bathgate & Clydebank & Coatbridge & Aberdeen & Greenock & Kilmarnock
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
If Worksheets("What's New").Range("I1").Value = "Scotland Benefit Centre" Then
On Error Resume Next
With OutMail
.SentOnBehalfOfName = ""
.To = ScotlandBC
.CC = ""
.BCC = ""
.Subject = "Alert - New Change Hub Item"
.Body = strbody
.Display
End With
On Error GoTo 0
End If
Set OutApp = Nothing
Set OutMail = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
UserForm1.Hide
End SubAny help greatly appreciated
Thanks
