VBA / Duplication of Sheets/commands

  • Thread starter Thread starter Abdul
  • Start date Start date
A

Abdul

I am working on a sheet in which i created a macro.

It is a lenthy macro, macro does the following tasks:

1. Creates tables one sheet2 then create formulas in the tables then
copy formulas till row#900.
2. Then it inserts a new sheet (sheet1), gives it the name "Summary"
and then places the previously created tables' total on this summary
sheet.

I have created a toolbar button to execute this macro.

The problem is:

i have to distribute this file to other people.

How can i add the macro code (what are those codes?) to first search
the sheet "summary", if sheet is present then don't go ahead. Similarly
if "macro button" is in the toolbar, dont put another macro in the
toolbar.

This is it! I am trying to clear my situation, even then if it is not
clear please do ask.....I need help serioiusly!

Jabeer
 
For each sheet in Activeworkbook.sheets
if sheet.name="Summary"
'etc

Untried:
for each ctrl in application.commandbars("Blah")
if ctrl.caption = "this is the one" then
ctrl.delete
'etc
 
Another way:

dim Wks as worksheet

set Wks = nothing
on error resume next
set wks = worksheets("summary")
on error goto 0

if wks is nothing then
'keep going!
else
msgbox "gotta quit!"
exit sub
end if

========

For the control, one way:

Option Explicit
Sub testme()

Dim myCtrl As CommandBarControl

Set myCtrl = Nothing
On Error Resume Next
Set myCtrl = Application.CommandBars("worksheet menu bar") _
.Controls("tools").Controls("My Caption Here")
On Error GoTo 0

If myCtrl Is Nothing Then
'it isn't there
Else
MsgBox "It's there"
End If

End Sub

But it might be better to keep track when you load it and clean it up when
you're closing the workbook:

Option Explicit
Sub auto_close()
Dim myTag As String
myTag = "__myControl__"
Call deleteByTag(myTag)
End Sub

Sub auto_open()

Dim myOwnerCtrl As CommandBarControl
Dim myCtrl As CommandBarControl
Dim myTag As String

myTag = "__myControl__"

Set myOwnerCtrl = Application.CommandBars("worksheet menu bar") _
.Controls("tools")

Call deleteByTag(myTag)

Set myCtrl = myOwnerCtrl.Controls.Add(Type:=msoControlButton, _
temporary:=True)
With myCtrl
.Style = msoButtonCaption
.Caption = "My Caption Here"
.OnAction = ThisWorkbook.Name & "!mymacronamehere"
.Tag = myTag
End With
End Sub

Sub deleteByTag(myTag As String)

On Error Resume Next
Do
CommandBars.FindControl(Tag:="__myControl__").Delete
If Err.Number <> 0 Then
Exit Do
End If
Loop
Err.Clear
On Error GoTo 0

End Sub
 
Thanks for your reply.

But is there a simple method for this?

I am confuse in all this much stuff, i need some explanation.
Because i am a novice in VBA i am writng code with the help of "Macr
Recording" tool.

Will someone explain in easy steps?

Thanks you people in advance

Jabee
 
The two easy ways were at the top.

'Declare an object variable to be the correct type:
dim Wks as worksheet

'I like to initialize it to what I want (Nothing)
set Wks = nothing

'turn off error checking. There might be an error in the next line
'but the procedure will handle it.
on error resume next

'Set that object variable to thing that may exist (or may not)
set wks = worksheets("summary")

'turn error checking back on
on error goto 0

'check to see if that object variable assignment was successful.
'if the variable is still nothing, then the assignment failed
'if it was set to that thing, then that thing existed.
if wks is nothing then
'keep going!
else
msgbox "gotta quit!"
exit sub
end if

And the check for control uses the same idea.

Just toss the other stuff at the bottom (or keep it for later if you want).
 
Thanks Peterson!

Now i got it and it is working....
You solved my problem...

God bless you!

Regards
Jabee
 
Back
Top