VBA for dynamic sharepoint metadata excel workbook properties

brentcook

Board Regular
Joined
Nov 28, 2016
Messages
78
I have posted a couple of times needing help, no luck. So I tried to do it myself, and have been.....unsuccessful. Admittedly I am not very proficient and VBA. Wondering if anyone could take a look at what I got. Help me make the adjustments. Thanks in advance for any help!

Need to have Metadata insert into a cell when opening workbook. Then when saving, cell into property. I tried to just get it to work with a button and am not having any luck. Can someone point me in the right direction? Here's what I got.

Code:
Private Sub CommandButton1_Click()

For Each Prop In ThisWorkbook.ContentTypeProperties
If Prop.Name = “Admit” Then
Prop.Value = Cells(4, 3).Value
End If


End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
*Update*

I have gotten closer to success. But need one last bit of help. Here's the code that I have:

Code:
Private Sub Workbook_Open()

For Each prop In ThisWorkbook.ContentTypeProperties


    If prop.Name = "Room Number" Then Range("C10").Value = prop.Value
    If prop.Name = "Psychiatrist" Then Range("C6").Value = prop.Value
    If prop.Name = "Therapist" Then Range("C8").Value = prop.Value
    If prop.Name = "Admit Date" Then Range("C4").Value = prop.Value
    If prop.Name = "Family Meeting" Then Range("C14").Value = prop.Value
    If prop.Name = "Target DC" Then Range("C12").Value = prop.Value
    If prop.Name = "Discharge To" Then Range("C18").Value = prop.Value
    If prop.Name = "Discharge Date" Then Range("C16").Value = prop.Value
    If prop.Name = "Contact" Then Range("Q5").Value = prop.Value




Next prop


End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


For Each prop In ThisWorkbook.ContentTypeProperties


    If prop.Name = "Room Number" Then prop.Value = Range("C10").Value
    If prop.Name = "Psychiatrist" Then prop.Value = Range("C6").Value
    If prop.Name = "Therapist" Then prop.Value = Range("C8").Value
    If prop.Name = "Admit Date" Then prop.Value = Range("C4").Value
    If prop.Name = "Family Meeting" Then prop.Value = Range("C14").Value
    If prop.Name = "Target DC" Then prop.Value = Range("C12").Value
    If prop.Name = "Discharge To" Then prop.Value = Range("C18").Value
    If prop.Name = "Discharge Date" Then prop.Value = Range("C16").Value
    If prop.Name = "Contact" Then prop.Value = Range("Q5").Value
    
Next prop


DoEvents


End Sub

I just need to figure out how to skip the event if the (when saving) the cell is blank. If you have any ideas I would really appreciate the help!

Thanks in advance.
 
Upvote 0
You could use an AND with your IF check:

Code:
If prop.Name = "Room Number" AND Range("C10").Value <> "" Then prop.Value = Range("C10").Value
 
Upvote 0
I think I jumped the gun. I need it to Not run if its blank. Any ideas? (sorry for the preemptive celebration.)

Maybe there's more info I can share. It the line that is including Family Meeting.

It won't run if the cell is blank. I've tried to set the field to allow for custom answer and adjusted anything that I can think of in SharePoint. Not working. So if I can skip that line if there's nothing in the cell would be awesome.

Again, thanks in advance.
 
Last edited:
Upvote 0
I'm not understanding your additional info. For example, what is "it" that won't run if the cell is blank? Is this cell a normal cell or is there some kind of formatting going on (pulldown menu)?

Does this code skip the line if blank like you want?
Code:
[COLOR=#333333]If prop.Name = "Family Meeting" AND [/COLOR][COLOR=#333333]Range("C14").Value <> ""[/COLOR][COLOR=#333333] Then prop.Value = Range("C14").Value[/COLOR]
 
Upvote 0
I ended up messing with the Sharepoint end. There is a section, since its a date, that asks:

Default value:
<input name="Default" title="Default Value: None" id="onetidIONoDate" type="radio" checked="checked" value=""><label for="onetidIONoDate"> (None)<!-- --> </label>
<input name="Default" title="Default Value: Today's Date" id="onetidIOTodayDate" type="radio" value="[today]"><label for="onetidIOTodayDate"> Today's Date<!-- --> </label>
<input name="Default" title="Default Value: Enter date in M/D/YYYY format" id="onetidIOLiteralDefaultDate" type="radio" value="">
<input name="Date" type="HIDDEN"> <input name="OWS:Date:Date" title="Default Value" class="ms-input" id="idDate" size="12" maxlength="255" value=""> <select name="OWS:Date:Hours" title="Hours" class="ms-input"> <option value="0" selected="">12 AM </option><option value="1">1 AM </option><option value="2">2 AM </option><option value="3">3 AM </option><option value="4">4 AM </option><option value="5">5 AM </option><option value="6">6 AM </option><option value="7">7 AM </option><option value="8">8 AM </option><option value="9">9 AM </option><option value="10">10 AM </option><option value="11">11 AM </option><option value="12">12 PM </option><option value="13">1 PM </option><option value="14">2 PM </option><option value="15">3 PM </option><option value="16">4 PM </option><option value="17">5 PM </option><option value="18">6 PM </option><option value="19">7 PM </option><option value="20">8 PM </option><option value="21">9 PM </option><option value="22">10 PM </option><option value="23">11 PM </option></select> <select name="OWS:Date:Minutes" title="Minutes"><option selected="True" value="0">00 </option><option value="5">05 </option><option value="10">10 </option><option value="15">15 </option><option value="20">20 </option><option value="25">25 </option><option value="30">30 </option><option value="35">35 </option><option value="40">40 </option><option value="45">45 </option><option value="50">50 </option><option value="55">55 </option></select>
blank.gif

Enter date in M/D/YYYY format.
<input name="Default" title="Default value: Calculated Value" id="onetidIOLiteralDefaultFormula" type="radio" value=""><label for="onetidIOLiteralDefaultFormula"> Calculated Value:</label>
<input name="DefaultFormula" title="Default value: Calculated Value" class="ms-input" id="onetidIODefTextValue03" type="text" maxlength="355" value="">

I just needed to turn to None for default and it works perfect. Thanks for your help.

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Hi, so what was you final solution with this in the end?
 
Upvote 0
Opening:

Private Sub Workbook_Open()

For Each prop In ThisWorkbook.ContentTypeProperties

If prop.Name = "Room" Then Range("C10").Value = prop.Value



Closing:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

For Each prop In ThisWorkbook.ContentTypeProperties

If prop.Name = "Room" Then prop.Value = Range("C10").Value



Make sure that your sharepoint settings match up. A lot of it is trial and error.

Let me know if you have any other questions.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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