How and where to save my VBA code to use in all my excel files

J

jurcek24

New Member
Joined
Feb 11, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi, I have the code for a macro but i need to send it to a few people and they want to use it in more files. Is there a way they could it just save it in the Excel programm files and the macro would just work in all excel files they open. I'm very new to this so if you can describe it step by step or a link how it's done. Thanks to everyone
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Save macro code file in ".xlam" ( Excel Addins) format

and Add that file in Excel Addins from Developer tab > Excel Add-Ins
 
Upvote 0
jurcek24 said:
Hi, I have the code for a macro but i need to send it to a few people and they want to use it in more files. Is there a way they could it just save it in the Excel programm files and the macro would just work in all excel files they open. I'm very new to this so if you can describe it step by step or a link how it's done. Thanks to everyone
Click to expand...
If you put your macro in your Personal folder it will work on all workbooks that you open.
Now the script may need to be generic in that it should refer to sheet names in a generic nature in case all users do not have same sheet names and such.

The scripts like this I use I assign a keyboard shortcut to makes it easy to run not needing a button to click.

You may want to post your code here for us to look at and see if we see any problems that may jump up if all users do not have same sheet names or other controls.
 
Upvote 0
Private Sub CommandButton1_Click()

Dim i As Long

Columns(1).Font.Color = vbBlack
Columns(9).EntireColumn.Delete

'For i = 1 To Rows.Count
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Pobarva na RDEČE kjer je številka 8
For i = 1 To 1000
If Cells(i, 1).Value = 8 And Not IsEmpty(Cells(i, 1).Value) Then
Cells(i, 3).Font.Color = vbRed
Cells(i, 1).Font.Color = vbRed
Cells(i, 8).Font.Color = vbWhite
Cells(i, 8).Interior.Color = vbRed
Cells(i, 1).Font.Bold = True
Cells(i, 3).Font.Bold = True


End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Pobarva na ZELENO kjer je številka 9

Next i

Dim j As Long


For j = 1 To 1000
If Cells(j, 1).Value = 9 And Not IsEmpty(Cells(j, 1).Value) Then
Cells(j, 1).Font.Color = vbGreen
Cells(j, 3).Font.Color = vbBlack
Cells(j, 8).Font.Color = vbBlack
Cells(j, 3).Interior.Color = vbGreen
Cells(j, 8).Interior.Color = vbGreen
Cells(j, 1).Font.Bold = True
Cells(j, 3).Font.Bold = True
Cells(j, 8).Font.Bold = True


End If


Next j
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Pobarva na TEMNO ZELENO kjer je številka 7

Dim k As Long


For k = 1 To 1000
If Cells(k, 1).Value = 7 And Not IsEmpty(Cells(k, 1).Value) Then
Cells(k, 1).Font.Color = RGB(51, 204, 51)
Cells(k, 3).Font.Color = RGB(51, 204, 51)
Cells(k, 8).Font.Color = RGB(51, 204, 51)
Cells(k, 1).Font.Bold = True
Cells(k, 3).Font.Bold = True



End If


Next k

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Pobarva na MODRO kjer je številka 6
Dim l As Long


For l = 1 To 1000
If Cells(l, 1).Value = 6 And Not IsEmpty(Cells(l, 1).Value) Then
Cells(l, 1).Font.Color = RGB(47, 117, 181)
Cells(l, 3).Font.Color = RGB(47, 117, 181)
Cells(l, 8).Font.Color = RGB(47, 117, 181)
Cells(l, 3).Font.Bold = True
Cells(l, 8).Font.Bold = True


End If


Next l

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Če je stolpec A st.5 in je B stolpec prazen, C stolpec ČRNO in BOLD
Dim x As Long


For x = 1 To 1000
If Cells(x, 1).Value = 5 And IsEmpty(Cells(x, 2).Value) Then
Cells(x, 1).Font.Color = vbBlack
Cells(x, 3).Font.Color = vbBlack
Cells(x, 3).Font.Bold = True


End If


Next x

End Sub





This is the code. Can you fix it so i don't need the CommandButton. i want to just pres ctrl+shift+c to start it
 
Upvote 0
To run a script with a shortcut key. The script must not be a Private script.
Remove the part of your script that says Private.
It should look like this:
Sub CommandButton1_Click()
Then:
Look on the Ribbon and click on View Macros
Click on your Macro and choose Options
Then enter the short cut key you want to use.
But to install a script into your personal workbook to use on all workbooks.
The script must be what is know as a module script. If you need help on this let me know.
 
Upvote 0
ok i got it to work with a key shortcut, now how do i make i so i can use it on all my excel files without having to copy the code everytime. Also i have to send it to my coworker to use as well
 
Upvote 0
jurcek24 said:
ok i got it to work with a key shortcut, now how do i make i so i can use it on all my excel files without having to copy the code everytime. Also i have to send it to my coworker to use as well
Click to expand...
Show me the script as you have it now.

Did you save it as a Module script.
Do you know what Module script means.
And I need to clean up your code as it should not need to be this long.
 
Upvote 0
Sub CommandButton1_Click()

Dim i As Long

Columns(1).Font.Color = vbBlack
Columns(9).EntireColumn.Delete

'For i = 1 To Rows.Count
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Pobarva na RDEČE kjer je številka 8
For i = 1 To 1000
If Cells(i, 1).Value = 8 And Not IsEmpty(Cells(i, 1).Value) Then
Cells(i, 3).Font.Color = vbRed
Cells(i, 1).Font.Color = vbRed
Cells(i, 8).Font.Color = vbWhite
Cells(i, 8).Interior.Color = vbRed
Cells(i, 1).Font.Bold = True
Cells(i, 3).Font.Bold = True

End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Pobarva na ZELENO kjer je številka 9

Next i

Dim j As Long


For j = 1 To 1000
If Cells(j, 1).Value = 9 And Not IsEmpty(Cells(j, 1).Value) Then
Cells(j, 1).Font.Color = vbGreen
Cells(j, 3).Font.Color = vbBlack
Cells(j, 8).Font.Color = vbBlack
Cells(j, 3).Interior.Color = vbGreen
Cells(j, 8).Interior.Color = vbGreen
Cells(j, 1).Font.Bold = True
Cells(j, 3).Font.Bold = True
Cells(j, 8).Font.Bold = True


End If


Next j
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Pobarva na TEMNO ZELENO kjer je številka 7

Dim k As Long


For k = 1 To 1000
If Cells(k, 1).Value = 7 And Not IsEmpty(Cells(k, 1).Value) Then
Cells(k, 1).Font.Color = RGB(51, 204, 51)
Cells(k, 3).Font.Color = RGB(51, 204, 51)
Cells(k, 8).Font.Color = RGB(51, 204, 51)
Cells(k, 1).Font.Bold = True
Cells(k, 3).Font.Bold = True



End If


Next k

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Pobarva na MODRO kjer je številka 6
Dim l As Long


For l = 1 To 1000
If Cells(l, 1).Value = 6 And Not IsEmpty(Cells(l, 1).Value) Then
Cells(l, 1).Font.Color = RGB(47, 117, 181)
Cells(l, 3).Font.Color = RGB(47, 117, 181)
Cells(l, 8).Font.Color = RGB(47, 117, 181)
Cells(l, 3).Font.Bold = True
Cells(l, 8).Font.Bold = True


End If


Next l

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Če je stolpec A st.5 in je B stolpec prazen, C stolpec ČRNO in BOLD
Dim x As Long


For x = 1 To 1000
If Cells(x, 1).Value = 5 And IsEmpty(Cells(x, 2).Value) Then
Cells(x, 1).Font.Color = vbBlack
Cells(x, 3).Font.Color = vbBlack
Cells(x, 3).Font.Bold = True


End If


Next x


End Sub


its the same as before. It's my first time doing this so if you can help that would be great yes.
No, I don't really know what it means
 
Upvote 0
Your script is looping through 1000 rows about four 5 times.
We should not need to do this.

Try this script:
VBA Code: 
Sub My_Script()
'Modified  2/13/2020  8:16:41 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Columns(1).Font.Color = vbBlack
Columns(9).EntireColumn.Delete
For i = 1 To Lastrow
Select Case Cells(i, 1).Value
    Case "8"
        Cells(i, 3).Font.Color = vbRed
        Cells(i, 1).Font.Color = vbRed
        Cells(i, 8).Font.Color = vbWhite
        Cells(i, 8).Interior.Color = vbRed
        Cells(i, 1).Font.Bold = True
        Cells(i, 3).Font.Bold = True
        
        Case "9"
            Cells(i, 1).Font.Color = vbGreen
            Cells(i, 3).Font.Color = vbBlack
            Cells(i, 8).Font.Color = vbBlack
            Cells(i, 3).Interior.Color = vbGreen
            Cells(i, 8).Interior.Color = vbGreen
            Cells(i, 1).Font.Bold = True
            Cells(i, 3).Font.Bold = True
            Cells(i, 8).Font.Bold = True
            
            Case "7"
                Cells(i, 1).Font.Color = RGB(51, 204, 51)
                Cells(i, 3).Font.Color = RGB(51, 204, 51)
                Cells(i, 8).Font.Color = RGB(51, 204, 51)
                Cells(i, 1).Font.Bold = True
                Cells(i, 3).Font.Bold = True
            Case "6"
                Cells(i, 1).Font.Color = RGB(47, 117, 181)
                Cells(i, 3).Font.Color = RGB(47, 117, 181)
                Cells(i, 8).Font.Color = RGB(47, 117, 181)
                Cells(i, 3).Font.Bold = True
                Cells(i, 8).Font.Bold = True
            Case "5"
                Cells(i, 1).Font.Color = vbBlack
                Cells(i, 3).Font.Color = vbBlack
                Cells(i, 3).Font.Bold = True
        End Select
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Shared workbook freezing when saving, but only for some.
Replies
0
Views
105
SingularitysPoint
S
P
  • Question
Signature is being removed when I run the rest of my VBA
Replies
3
Views
187
rollis13
rollis13
J
  • Question
Forced to discard digital signature after opening and immediately saving an Macro-Enabled Excel file
Replies
0
Views
126
JDCreate
J
K0st4din
  • Solved
How do I automate macros to be placed in many workbooks that won't be on my PC
2 3 4
Replies
33
Views
709
K0st4din
K0st4din
J
  • Question
VBA Script - Can't figure out one error...
2
Replies
15
Views
284
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,430
Messages
6,124,852
Members
449,194
Latest member
HellScout

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top