Issue with macro when using excel sheet with internet explorer

VBA-Idiot

New Member
Joined
Mar 14, 2012
Messages
17
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 -
Code:
Sub Rectangle25_Click()
    Application.ThisWorkbook.Sheets("What's New").Visible = True
    Application.ThisWorkbook.Sheets("What's New").Select
    UserForm1.Show

End Sub

Clicking "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 Sub

Any help greatly appreciated

Thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top