Hi all,
I have the following Code which works when i run it manually,
MODULE1
(It basically combines workbooks into one, This works and i have no problems)
This is my code for creating a button on the menu, and runs the SUB above when clicked.
THIS WORKBOOK
When i click my pre made button it then runs the SUB to Combine workbooks but i get the following error message....
Microsoft Excel
Method 'Move' of objects 'Sheets' failed
Any ideas?
The top SUB works when i run it manually, but not when i run it when i click the button.... i cant figure it out.
FYI This is a XLA Addin i have created.
Ty for any input.
Sam
I have the following Code which works when i run it manually,
MODULE1
Code:
Option Explicit
Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer
Dim ShCnt As Integer
Dim i As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Files to Merge")
If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If
x = 1
While x <= UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(x)
With ThisWorkbook
ShCnt = .Sheets.Count
Sheets().Move After:=.Sheets(ShCnt)
For i = ShCnt + 1 To .Sheets.Count
.Sheets(i).Range("I1") = Dir(FilesToOpen(x))
Range("I1").Select
Selection.Font.Bold = True
Next i
End With
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
(It basically combines workbooks into one, This works and i have no problems)
This is my code for creating a button on the menu, and runs the SUB above when clicked.
THIS WORKBOOK
Code:
Option Explicit
Dim sMenu As String
Private Sub Workbook_BeforeClose(Cancel As Boolean)
sMenu = "myButton"
On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0
End Sub
Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl
Dim newMenu As Object 'CommandBarControl
Dim ctrlButton As Object 'CommandBarControl
sMenu = "Merge Workbooks"
On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0
Set oCB = Application.CommandBars("Formatting")
Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)
With oCtl
.BeginGroup = True
.Caption = sMenu
.FaceId = 197
.Style = msoButtonIconAndCaption
.OnAction = "CombineWorkbooks"
End With
End Sub
When i click my pre made button it then runs the SUB to Combine workbooks but i get the following error message....
Microsoft Excel
Method 'Move' of objects 'Sheets' failed
Any ideas?
The top SUB works when i run it manually, but not when i run it when i click the button.... i cant figure it out.
FYI This is a XLA Addin i have created.
Ty for any input.
Sam