Luca,
Thank you for your reference. That article explained the reason for a
different problem that we have experienced. However, the current problem
seems to only occur on a few machines and only for those worksheets that have
combobox objects in them. The problem occurs at any attempt to
programatically copy worksheets with those characteristics. Have any ideas?
"Luca Brasi" wrote:
you might want to take a look he
http://support.microsoft.com/kb/210684
OssieMac wrote:
Hi Steve,
I had a problem posting this reply so it is possible you might get it twice.
I have been out of the picture for a couple of days and hence the late
reply. The code runs successfully on Windows Vista with xl2007 and Windows XP
SP2 and xl 2002 so I am sorry but I don't think that I can offer any more
help.
Regards,
OssieMac
"Steve Rosenblum" wrote:
OssieMac,
I appreciate your suggestions however the problem still exists. Let me be
more explicit.
First - your last post got it right. the worksheets are in the same
workbook but the macro that I am running is in a different workbook.
Second - your code and my original code run fine on many machines. There
are only a few machines that I have seen where it fails.
It seems to fail on only XP machines. To be more specific, one of the
machines that it fails on is Windows XP v. 5.1 SP2 running MS Excel 2003 -
SP2.
I was hoping that I could find a more general solution that would not be
operating system / software version dependent. That was why I didn't include
this information in my prior posts.
If you have any insight/thoughts I would appreciate hearing them.
Steve
"OssieMac" wrote:
Hi yet again Steve,
I have been wondering if I have misinterpreted your question and that you
meant that you want to copy the worksheet within the same workbook but from a
macro in another workbook. If this is correct then the macro I gave you is
basically the same except that you can inititalize i to zero because it then
counts the Equipment_Std that it is copying as one worksheet and therefore
the number would be correct for adding another sheet name Equipment + i.
The other line of code to change is:-
'Copy sheet in same workbook (wbData)
wbData.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")
Regards,
OssieMac
"OssieMac" wrote:
Hi again Steve,
Your original post said that you wanted to copy from one workbook to
another. The following line of code you posted is to the same workbook:-
DataWorkbook.Sheets(Base + "_Std").Copy _
Befo=DataWorkbook.Sheets("Results")
Perhaps you have been working on trying to find the problem.
However, I have edited you code and made a few changes. Firstly it is not a
good idea to use words like WorkSheet as variables. (The name describes the
type of variable.)
I assumed that "Equipment" is passed to Base when the sub is called. As you
will see, I set up a dummy procedure to call the sub and pass the name to the
procedure.
I have tested the code below and it copies combo boxes with the worksheets.
(Tested with both Forms Combo boxes and ActiveX Combo Boxes) . However, if
you have linked ranges in the combo boxes linked to another worksheet in the
original workbook, after copying, it links those ranges back to the original
workbook. This does not occur if the linked ranges are on the same worksheet
being copied.
It has been tested with xl2007 and and on a separate computer with xl2002
(XP). Edit the "Set wbData =" line of code as per comment if not using
xl2007.
I disabled error handling mainly for the purpose of testing so that I could
see what was failing. Feel free to put it back in because all programs should
have some sort of error handling for the end user so they are not dropped
into the code and have no idea why.
Sub Calling_procedure()
Call AddWorksheet("Equipment")
End Sub
Sub AddWorksheet(Base As String)
'On Error GoTo errorhandler
Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'DataWorkbook
Dim ws As Worksheet 'Variable for Each loop
Dim i As Integer 'For/Next loop
Set wbThis = ThisWorkbook
'xlsx extension is xl2007.
'Use xls for earlier versions
Set wbData = Workbooks.Item("DataWorkbook.xlsx")
'Following line needs to = 1 so that value finishes
'as 1 greater than what already exists for new sheet
i = 1
wbData.Activate
For Each ws In Worksheets
If ws.Name Like Base + "*" Then
i = i + 1
End If
Next ws
'Copy sheet from ThisWorkbook to wbData
wbThis.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")
ActiveSheet.Name = "Equipment " + CStr(i)
ActiveSheet.Visible = True
'Following line will return to the workbook
'containing this macro if required
'wbThis.Activate
Exit Sub
'errorhandler:
' Call MsgBox("Problem with AddWorksheet. " + _
' "You must close Excel and then reopen " + _
' "the tool to continue", vbCritical + _
' vbOKOnly, "EXCEL ADD WORKSHEET Error ")
' If DebugFlag Then
' MsgBox ("Error in " + "AddWorksheet")
' End If
End Sub
Feel free to get back to me if still having problems.
Regards,
OssieMac