ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Convert macro code to an addin (https://www.excelbanter.com/excel-programming/423724-convert-macro-code-addin.html)

ArtySin

Convert macro code to an addin
 
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

ArtySin

Convert macro code to an addin
 
I forgot to add that this is for Excel 2000 (old corporate s/ware)

"ArtySin" wrote:

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


ryguy7272

Convert macro code to an addin
 
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" wrote:

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

"ArtySin" wrote:

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


ArtySin

Convert macro code to an addin
 
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" wrote:

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" wrote:

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

"ArtySin" wrote:

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


ryguy7272

Convert macro code to an addin
 
Look here, last section:
http://www.contextures.com/xlToolbar02.html

Regards,
Ryan---

--
RyGuy


"ArtySin" wrote:

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" wrote:

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" wrote:

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

"ArtySin" wrote:

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


Jim Cone[_2_]

Convert macro code to an addin
 
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"

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

ArtySin

Convert macro code to an addin
 
Ryan thanks V much for the help all now works a treat
Rgds
ArtySin

"ryguy7272" wrote:

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

Regards,
Ryan---

--
RyGuy


"ArtySin" wrote:

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" wrote:

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" wrote:

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

"ArtySin" wrote:

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


ArtySin

Convert macro code to an addin
 
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

"Jim Cone" wrote:

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"

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



All times are GMT +1. The time now is 10:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com