| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Urgent Pls, how to convert an addin xlam file to xls worksheet in | Excel Discussion (Misc queries) | |||
| Convert Excel VBA addin to a Protected Format | Excel Programming | |||
| Convert Excel VBA addin to a Protected Format | Excel Programming | |||
| Connect a COM AddIn from VBA Code | Excel Programming | |||
| using code from an addin | Excel Programming | |||
Linear Mode
