Embedded Excel File in Word Returns Method 'Range' of object'_Global' failed

Insurance Dave

New Member
Joined
Jul 1, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello All,

It has been a while since I have been here (had to create a new account). Good to be back. I have limited VBA knowledge (MrExcel and self-taught).

I have a Form - named 'Initial'- that opens when the workbook is opened. When opened from my PC there are no issues. The Excel file needs to be embedded into a Word Document for access to others. When it is embedded into a Word document and opened, I receive:

Run-time error '1004':
Method 'Range' of object'_Global' failed


Any ideas on why it only does this with the embedded version? Is there a better code for this?


The code for opening the Form is:

Private Sub Workbook_Open()
Initial.Show
End Sub


The form has two button options. One is to clear the data entry cells on a specific sheet. The other is to open the workbook with the previously saved data. The code is:

Private Sub Clear_Click()
With ThisWorkbook.Worksheets("Calculator")
Range("E6:F6").ClearContents
Range("B10:D10").ClearContents
Range("B16:D16").ClearContents
Range("B19:D19").ClearContents
Range("B22:D22").ClearContents
Range("B13:D13").ClearContents
Range("B25").ClearContents
End With
Unload Initial
End Sub


And the code to open with the previous data is just:

Private Sub OpenSheet_Click()
Unload Initial
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,035
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Your With block is currently not actually doing anything. The code should read:

Code:
Private Sub Clear_Click()
With ThisWorkbook.Worksheets("Calculator")
.Range("E6:F6").ClearContents
.Range("B10:D10").ClearContents
.Range("B16:D16").ClearContents
.Range("B19:D19").ClearContents
.Range("B22:D22").ClearContents
.Range("B13:D13").ClearContents
.Range("B25").ClearContents
End With
Unload Me
End Sub

and:
Code:
Private Sub OpenSheet_Click()
Unload Me
End Sub
 

Insurance Dave

New Member
Joined
Jul 1, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi Rory,

Thank you for your quick response. I have changed the code, and it does seem to work properly now. Thank you for your help!

Dave
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,035
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,313
Members
425,272
Latest member
Umba

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