Solved

Make excel macro available to all users

Posted on 2014-03-11
18
778 Views
Last Modified: 2014-03-19
Hello,

How do you make excel macro available to all users?

Refer to link for macro.

Thanks

Excel insert Picture through macro
0
Comment
Question by:rha_mtl
  • 9
  • 8
18 Comments
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 39920785
Hi,
To excel macro available to all users. you need to make it as addins. Please go through the below article.

http://www.ozgrid.com/VBA/excel-add-in-create.htm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39921328
Question:
By other users what do you mean ?

1) Users of same PC
2) Any User that uses this file on a server ?

gowflow
0
 

Author Comment

by:rha_mtl
ID: 39921340
All users with their own computer on a domain that will access the file via a server
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39921369
then the file is on a shared drive lets say H and is named let's say Picture.xlsm so we have
H:\Picture.xlsm that is on the server

For users to have access to this file they need to have full control on it and then they can access it.

For sure all sharing that apply to Excel will apply here like if the file is opened by 1 user, then it will flag an other user trying to opening it telling him file will open on Readonly basis until user x has close it.

I do not think that this is what your looking for.

I guess you need to explain why you need to have this file that have a picture that can be shown or hidden with a CTRL be accessible to all users ? to do what ? You maybe need a Template here ! and this is different.

We can also make it addin but here again the way it is will not function as addin we need to modify it but first I need to have a clear understanding on what do you intent to do with the file and why you need users to have access to it ?

gowflow
0
 

Author Comment

by:rha_mtl
ID: 39921887
OK.

What the user would like is the following

We have multiple excel files some from clients and some from us. The user would like to be able to identify within the excel document that states that it is ours or the clients. So they would like to be able to insert our logo in cell A1. This way clearly identifying the document when it is opened will be clearly obvious.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39922025
Well the macro that was developed does not do this at all. What you are asking is totally different.

ok here is the code for that

Sub CopyPicture()
Dim WSThis As Worksheet
Dim WS As Worksheet
Dim Img As OLEObject
Dim WSImg As OLEObject

Set WS = ActiveSheet
Set WSThis = ThisWorkbook.Worksheets("Main")

On Error Resume Next
Set WSImg = WS.OLEObjects("Image1")
If Err = 0 Then
    
Else
    Set Img = WSThis.OLEObjects("Image1")
    Img.Copy
    WS.Range("A1").PasteSpecial
End If
On Error GoTo 0

End Sub

Open in new window


and here is how it works:

1) Save this file onto your pc
2) Open the file and choose saveas and from the types select Addin *.xlsam
3) close the file
4) Open blank Excel choose Options then Addins then Press on Go in the bottom of the windows it will open a list of Addins make a Tick on this file Addin-Logo press ok and close Excel
5) Open any workbook that you want to include the logo
6) on any sheet that you want press CTRL L and the logo will be affixed on A1 (now you will have the Stop sign)
7) Try again on the same sheet an other time CTRL L it should do nothing like if the logo is there it will not put it again.

Try any other file.

Let me know if it is ok and working then I can guide you to changing to your logo.
gowflow
Addin-Logo.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39923564
Any chance to have tried out the proposed solution ?
gowflow
0
 

Author Comment

by:rha_mtl
ID: 39923708
When I open the attached file, it opens with the image in the center sort of. Over cells,please see attached screenshot.

I also tried the ctl L and it didn't work.
screen-shot.jpg
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39923782
NORMAL !!!!

Follow my instructions. This file means nothing it is only there to use it as an Addin.
Do exactly what I said follow the steps then you will see the final result.

On purpose I put it in the middle to make sure it is not picking up this file !!!! pgming technique. :)

gowflow
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rha_mtl
ID: 39923838
Ah yes it works.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39923888
Once you finish testing it, I will tell you how to change this picture to show your logo.
gowflow
0
 

Author Comment

by:rha_mtl
ID: 39924006
OK it works well. I finished testing
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39924152
ok here it is:

1) We need to disable the Addin to make changes. Any time you need to modify the Addin file you need to disconnect from the Addin. You have to rember this very well as you now intend to distribute this to all your users. So you need to make sure that the file we put in the Addin is FINAL and no alteration to it or else you will need to do all the below step on each and every single machine that you installed the Addin on it.

2) To stop the Addin, Open blank Excel goto File options click on Addin click on Go at the bottom and in the opened window deselect Addin-Logo. Click ok and close Excel.

3) Now that we have disconnected the Addin we will modify the file. Load the file that I attached Addin-Logo.xlsm (it is your main file that we can modify)

4) Once loaded macros are enabled we need to go to design mode on the developer tab click on the Icon design it is like a triangle

5) right Click on the Picture and select Properties

6) In the properties you will see on called Picture click on it and press on the 3 points to select your logo locate the file on your disk and press ok.

7) Size the logo to your taste and leave it in the middle of the sheet.

8) Once all changes are done save the file first as xlsm like click on save.

9) Now we need to update the Addin Choose from File Saveas and in the type of choose Addin *.xlsam and press ok Leave the same name as the previous one Addin-Logo it wil ask you to override the Existing Addin file say yes.

10) Close Excel

11) Open a blank Excel and goto File Options / Addin / GO and Select Addin-Logo. press OK Close Excel.

12) Now your machine is ready and have the Logo activated as Addin try it on any workbook.


For the rest of the users you will need to do the following steps:
If addin already installed on the User Machine
Step 2) Above: Disable the Addin
then
Open the Addin-Logo.xlsm that should be located on the server (from the user machine) and saveas *.xlsam
Close the File
Step 11) and 12) above


Let me know.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39926203
Any chance to have tried it out ?
gowflow
0
 

Author Comment

by:rha_mtl
ID: 39926405
not yet i will today
0
 

Author Closing Comment

by:rha_mtl
ID: 39926512
Excellent it works great.

Thanks for all your help
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39926730
Your welcome glad I could help.
gowflow
0
 

Author Comment

by:rha_mtl
ID: 39939432
Hello,

I forgot to ask is there a way to undo this by typing ctl + L. Right now that function doesnt work.

Do i need to ask another question?

Thanks
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now