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

[Insurance Dave]

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

 

[RoryA]

Your With block is currently not actually doing anything. The code should read:

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:​

Private Sub OpenSheet_Click()
Unload Me
End Sub

 

[Insurance Dave]

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]

Glad to help.