Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have created an add-in using VBA which is a workbook with calculations. The add-in has a userform to extract relevant information from access database and populates the workbook. After the data is populated, calculations are performed in Sheet1. I need to paste the worksheet "Sheet1" from the add-in worksheet to a new workbook on running the add-in macro.

When I run the add-in however, the worksheet appears to be hidden so my data is not updating. I get this error: " Run-time error '1004': Method 'Worksheets' of object '_Global' failed".

Can someone tell me how to work with an add-in which has a worksheet where the required calculations are performed?

The intriguing part is when I load the add-in after removing it from the list of add-ins in excel, it runs perfectly. But when I re-run the macro, the worksheet becomes hidden, so the same error appears. I am fairly new to VBA so any suggestions would be appreciated!

Edit

Code:

Private Sub OptionOK_Click() 'On selecting OK from userform
  Dim ws1 As Worksheet
  Sheets("Sheet1").Visible = True 
  Set ws1 = Worksheets("Sheet1") 

 'User Form Validation 
  If Trim(Me.cboData.value) = "" Then 
    Me.cboData.SetFocus 
    MsgBox "Please complete the form" 
    Exit Sub 
  End If 

 'copies data to given cell in excel     
  ws1.Range("A1").value = Me.cboData.value 

 'To copy selection from "Sheet1" into new workbook 
Workbooks("myaddin.xlam").Sheets(1).Copy 
End Sub

I get the error on ...> Sheets("Sheet1").Visible = True.

share|improve this question
add comment

2 Answers

up vote 0 down vote

If you know what sheet it is and you have access to the add-in code just make sure it's visible before the line that throws the error.

Sheets("Sheet3").Visible = True

I suspect you have another problem though because you can still reference a hidden sheet in code.

Are you sure this line is correct:

Workbooks("myaddin.xlam").Sheets(1).Copy

Before you were referencing the name of the sheet now your referencing the position of the sheet in the workbook.

share|improve this answer
 
Thank you, I did try it. I have Dim ws1 As Worksheet Worksheets("Sheet1").Visible = True Set ws1 = Worksheets("Sheet1") I get this error: Runtime error 9, Subscript out of range. I have searched online too but I don't see any related solutions. –  VBAlearner Oct 1 '13 at 21:19
 
Thanks for the edit. cbo is combobox in the userform. me.cboData refers to the combobox which uses access database to form a list. –  VBAlearner Oct 1 '13 at 22:04
 
Yes I believe we can reference worksheets("Sheet1") or worksheets(1). Like I mentioned, the add-in runs perfectly when I uninstall it and install it again in the excel. It hangs up on me when I run the macro after the first time. –  VBAlearner Oct 1 '13 at 22:14
 
Yes, you can but be sure that "Sheet1" is actually worksheets(1) since someone can rename it. You could try Sheets(1).Visible = True –  Portland Runner Oct 1 '13 at 22:20
add comment
up vote 0 down vote accepted

I just realized that I had to use "ThisWorkbook" in the add-in VBA code.

Set ws1 = ThisWorkbook.Sheets ("Sheet1")

VBA code within a workbook should use "ThisWorkbook" to reference to sheets or ranges inside the add-in.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.