Convert macro code to an addin

A

ArtySin

I have created a macro which works perfectly for me but now need to send it
to about 100 others across the planet so they can use it. I could just send
them the macro worksheet and they have to have that open and run it.
However, they also have to run an add-in which when installed just appears
under the 'Data' menu and I'd like to be able to convert this macro to an
add-in and have that appear under the 'Data' menu too. Can anyone help
please?
Cheers
ArtySin
BTW here's the code if it helps:

Sub Insert_Headers()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Plan:Test Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Execute Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Tester"
Range("F1").Select
Columns("D:D").Select
Selection.NumberFormat = "h:mm"
Range("A1:E1").Select
Selection.Font.Bold = True
Range("A1").Select

Dim brkt As Range
Set brkt = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each C In brkt
C.Value = Mid(C.Value, InStr(C.Value, "]") + 1)
Next

Dim quote As Range
Set quote = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each C In quote
C.Value = Mid(C.Value, InStr(C.Value, "'") + 1)
Next

End Sub
 
R

ryguy7272

You need to save it as an .xla file (Excel Add In). See this:
http://www.fontstuff.com/VBA/vbatut03.htm

Regards,
Ryan---
--
RyGuy


ArtySin said:
I forgot to add that this is for Excel 2000 (old corporate s/ware)

ArtySin said:
I have created a macro which works perfectly for me but now need to send it
to about 100 others across the planet so they can use it. I could just send
them the macro worksheet and they have to have that open and run it.
However, they also have to run an add-in which when installed just appears
under the 'Data' menu and I'd like to be able to convert this macro to an
add-in and have that appear under the 'Data' menu too. Can anyone help
please?
Cheers
ArtySin
BTW here's the code if it helps:

Sub Insert_Headers()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Plan:Test Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Execute Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Tester"
Range("F1").Select
Columns("D:D").Select
Selection.NumberFormat = "h:mm"
Range("A1:E1").Select
Selection.Font.Bold = True
Range("A1").Select

Dim brkt As Range
Set brkt = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each C In brkt
C.Value = Mid(C.Value, InStr(C.Value, "]") + 1)
Next

Dim quote As Range
Set quote = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each C In quote
C.Value = Mid(C.Value, InStr(C.Value, "'") + 1)
Next

End Sub
Click to expand...
Click to expand...
 
A

ArtySin

Hi Ryan,
Yes I did that following the instructions but cant get it to appear on the
'Data' menu or anywhere else for that matter. Any ideas?
Cheers
ArtySin

ryguy7272 said:
You need to save it as an .xla file (Excel Add In). See this:
http://www.fontstuff.com/VBA/vbatut03.htm

Regards,
Ryan---
--
RyGuy


ArtySin said:
I forgot to add that this is for Excel 2000 (old corporate s/ware)

ArtySin said:
I have created a macro which works perfectly for me but now need to send it
to about 100 others across the planet so they can use it. I could just send
them the macro worksheet and they have to have that open and run it.
However, they also have to run an add-in which when installed just appears
under the 'Data' menu and I'd like to be able to convert this macro to an
add-in and have that appear under the 'Data' menu too. Can anyone help
please?
Cheers
ArtySin
BTW here's the code if it helps:

Sub Insert_Headers()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Plan:Test Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Execute Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Tester"
Range("F1").Select
Columns("D:D").Select
Selection.NumberFormat = "h:mm"
Range("A1:E1").Select
Selection.Font.Bold = True
Range("A1").Select

Dim brkt As Range
Set brkt = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each C In brkt
C.Value = Mid(C.Value, InStr(C.Value, "]") + 1)
Next

Dim quote As Range
Set quote = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each C In quote
C.Value = Mid(C.Value, InStr(C.Value, "'") + 1)
Next

End Sub
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Look here, last section:
http://www.contextures.com/xlToolbar02.html

Regards,
Ryan---

--
RyGuy


ArtySin said:
Hi Ryan,
Yes I did that following the instructions but cant get it to appear on the
'Data' menu or anywhere else for that matter. Any ideas?
Cheers
ArtySin

ryguy7272 said:
You need to save it as an .xla file (Excel Add In). See this:
http://www.fontstuff.com/VBA/vbatut03.htm

Regards,
Ryan---
--
RyGuy


ArtySin said:
I forgot to add that this is for Excel 2000 (old corporate s/ware)

:

I have created a macro which works perfectly for me but now need to send it
to about 100 others across the planet so they can use it. I could just send
them the macro worksheet and they have to have that open and run it.
However, they also have to run an add-in which when installed just appears
under the 'Data' menu and I'd like to be able to convert this macro to an
add-in and have that appear under the 'Data' menu too. Can anyone help
please?
Cheers
ArtySin
BTW here's the code if it helps:

Sub Insert_Headers()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Plan:Test Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Execute Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Tester"
Range("F1").Select
Columns("D:D").Select
Selection.NumberFormat = "h:mm"
Range("A1:E1").Select
Selection.Font.Bold = True
Range("A1").Select

Dim brkt As Range
Set brkt = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each C In brkt
C.Value = Mid(C.Value, InStr(C.Value, "]") + 1)
Next

Dim quote As Range
Set quote = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each C In quote
C.Value = Mid(C.Value, InStr(C.Value, "'") + 1)
Next

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

Jim Cone

Sending out code/add-ins to 100 people can be risky.
Test the code extensively first and then try it out on a few co-workers.
Never send out code without error handling in it.

With your posted code...
What happens if the worksheet is protected?
What happens if the last row on the sheet has data in it.
What happens if someone wants their original data back the way it was?

Will all of the 100 people know how to install the add-in?
What will you do when you get requests/orders to change or expand the code?

Life can be difficult. <g>
--
Jim Cone
Portland, Oregon USA



"ArtySin"
<[email protected]>
wrote in message
I have created a macro which works perfectly for me but now need to send it
to about 100 others across the planet so they can use it. I could just send
them the macro worksheet and they have to have that open and run it.
However, they also have to run an add-in which when installed just appears
under the 'Data' menu and I'd like to be able to convert this macro to an
add-in and have that appear under the 'Data' menu too. Can anyone help
please?
Cheers
ArtySin
BTW here's the code if it helps:

Sub Insert_Headers()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Plan:Test Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Execute Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Tester"
Range("F1").Select
Columns("D:D").Select
Selection.NumberFormat = "h:mm"
Range("A1:E1").Select
Selection.Font.Bold = True
Range("A1").Select

Dim brkt As Range
Set brkt = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each C In brkt
C.Value = Mid(C.Value, InStr(C.Value, "]") + 1)
Next

Dim quote As Range
Set quote = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each C In quote
C.Value = Mid(C.Value, InStr(C.Value, "'") + 1)
Next

End Sub
 
A

ArtySin

Ryan thanks V much for the help all now works a treat
Rgds
ArtySin

ryguy7272 said:
Look here, last section:
http://www.contextures.com/xlToolbar02.html

Regards,
Ryan---

--
RyGuy


ArtySin said:
Hi Ryan,
Yes I did that following the instructions but cant get it to appear on the
'Data' menu or anywhere else for that matter. Any ideas?
Cheers
ArtySin

ryguy7272 said:
You need to save it as an .xla file (Excel Add In). See this:
http://www.fontstuff.com/VBA/vbatut03.htm

Regards,
Ryan---
--
RyGuy


:

I forgot to add that this is for Excel 2000 (old corporate s/ware)

:

I have created a macro which works perfectly for me but now need to send it
to about 100 others across the planet so they can use it. I could just send
them the macro worksheet and they have to have that open and run it.
However, they also have to run an add-in which when installed just appears
under the 'Data' menu and I'd like to be able to convert this macro to an
add-in and have that appear under the 'Data' menu too. Can anyone help
please?
Cheers
ArtySin
BTW here's the code if it helps:

Sub Insert_Headers()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Plan:Test Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Execute Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Tester"
Range("F1").Select
Columns("D:D").Select
Selection.NumberFormat = "h:mm"
Range("A1:E1").Select
Selection.Font.Bold = True
Range("A1").Select

Dim brkt As Range
Set brkt = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each C In brkt
C.Value = Mid(C.Value, InStr(C.Value, "]") + 1)
Next

Dim quote As Range
Set quote = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each C In quote
C.Value = Mid(C.Value, InStr(C.Value, "'") + 1)
Next

End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

ArtySin

Jim,
Very good points you raise. However, the w/sheet won't be protected as each
user will start a fresh one for themselves and the last rows will not have
any data in them. We also will be packaging up the .xla file as most of the
users do not have write access to the drive.
Rgds & Thanks
ArtySin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro help 0
Insert columns and values programmatically with IF statement 9
Advice on multiple macros, same code just a different column 9
criteriarange not equal to 4
How to make this code smaller 6
Different Results from the Same Macro 3
Macro to Select text from a point given to a specific word 2
Creating Pivot Table with Macro using Multiple Sheets 0

Top