Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Popup Work Book Author's Name

-1

Sir,

Sorry for my previos post; please pardon me for posting in all caps font.

How I can embade program within one workbook so that work book Author's name is Popped up in  the computer of a particular recepient of the workbook (via mail) or in all the computer of any recepient other than any specific recepient of the work book. 

Regrds

Barenya Sanyal

Answer
Discuss

Discussion

Don will not perceive your shouting at him as respectful. As for myself, I will not answer a shouted question. I suggest that you amend your post to avoid all-caps so that your tone will not be misunderstood by anyone.
Variatus (rep: 4889) Jul 9, '18 at 5:06 am
C'mon man, everyone knows not to post things in ALL CAPS! Just edit your question so it's not in caps and add some clarification on how, specifically you want it to work. I think this could be a cool little macro that is easy to implement.
don (rep: 1989) Jul 10, '18 at 6:29 am
Add to Discussion

Answers

0

The attached workbook will show a message when you open it, and this is the code that does the job.

Private Sub Workbook_Open()
    ' 13 Jul 2018
    
    ' Note: There is a limit to the width you can use:
    Const MsgWidth As Long = 45             ' number of characters
    Dim Prop As String
    Dim Msg As String
    
    ' Set the property
    ThisWorkbook.BuiltinDocumentProperties("Author") = "Mickey Mouse"
    
    ' recover the property value
    Prop = ThisWorkbook.BuiltinDocumentProperties("Author")
    
    ' construct the message
    Msg = String(MsgWidth, "+") & _
          String(2, vbCr) & String(28, " ") & _
          "This workbook was created by" & _
          String(2, vbCr) & String(40, " ") & _
          Prop & String(2, vbCr) & _
          String(MsgWidth, "+")
    MsgBox Msg, vbInformation Or vbOKOnly, "Important Information"
End Sub

You will find the code in the ThisWorkbook module. Press Alt+F11 to open the VB Editor and navigate to the code module there. Observe that the workbook is macro-enabled. You will have a demonstration of why your idea is only of limited feasibility with this test.

Macros can be dangerous to your computer. Therefore Excel disables them by default. When you open the attached workbook you may be prompted to confirm that you want to allow macros to run. In fact, your security settings may even disable this question. Therefore the message may not run for everyone. If it doesn't run for you check your macro security settings.

Second, the attached workbook has all personal information removed from it. That is the standard for published workbooks, and it includes the author's name. I was unable to publish a workbook here with the Author's property set. Hence the lines to set the author's name in the first place.

    ' Set the property
    ThisWorkbook.BuiltinDocumentProperties("Author") = "Mickey Mouse"

When you deploy this macro in your workbook the "Author" property should be set manually and these lines of code deleted from the macro. You can just enter an apostroph at the beginning of the line to make it ineffective.

Of course, anyone can set the Author property and thereby change the message. You may use the above code to undo any change the user might make and reinstate the author's name. Or you might use this line of code to bypass the built-in property altogether. Change the existing line of code Prop = ThisWorkbook.BuiltinDocumentProperties("Author") to Prop = "Mickey Mouse" and that name will appear in the message.

I have created a rough design for the message which you can modify, of course. Modify the total width Const MsgWidth As Long = 45. Note that the maximum width Excel allows depends upon the character you choose. I chose "+" and for that character 45 is max on my machine and my settings for Excel.

String(MsgWidth, "+") creates a string of [45] repetitions of "+"
String(2, vbCr) & String(28, " ") inserts 2 x [Enter] followed by 28 spaces.
Observe the ampersand between the expressions to concatenate them into one string and the trailing underscore, preceded by a space, to write a concatenation in several lines of code.

Note that a more elaborate message would be possible by showing a Userform instead of a message box. However, the startup limitations are caused by VBA and can't be avoided. The applicable principle is that the supplier of the workbook can't take control of the recipient's computer for better or for worse. The recipient has to be allowed to decide which code he wants to allow to run on his machine.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login