Set Excel Document Properties

Somebody asked me whether it is possible to change the built document properties programmatically, that we can access for excel by going to worksheet menu bar… file >> properties… The Document Property window pops up and you can set the built in document properties by typing out on controls in summary tab. I thought it wont be possible, but then I found a way that this can be done.

I have listed the code below…

Option Explicit

Private Sub cmdAssign_Click()
Dim i As Integer
For i = 0 To Me.Controls.Count – 1
  If UCase$(TypeName(Controls(i))) = “TEXTBOX” Then
    With ActiveWorkbook
      .BuiltinDocumentProperties(Mid(Me.Controls(i).Name, 4, 255)) = Me.Controls(i).Text
      Me.Controls(i).Text = “”
    End With
  End If
Next i
MsgBox “Document Properties Successfully Done “
End Sub

Private Sub cmdCancel_Click()
Dim msg1
  msg1 = MsgBox(“Are you sure you want to quit this form?”, vbYesNo, “Exit This Form”)
  If msg1 = vbYes Then
    Unload Me
  End If
End Sub

To Download the example codes on userform CLICK HERE

To Download Example workbook Demonstrating the above code CLICK HERE

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: