VBA for dynamic sharepoint metadata excel workbook properties

[brentcook]

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.

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

 

[brentcook]

*Update*

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

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.

 

[shknbk2]

You could use an AND with your IF check:

If prop.Name = "Room Number" AND Range("C10").Value <> "" Then prop.Value = Range("C10").Value

 

[brentcook]

Thanks! Perfect

 

[shknbk2]

You're welcome.

 

[brentcook]

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.

 

[shknbk2]

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?

[COLOR=#333333]If prop.Name = "Family Meeting" AND [/COLOR][COLOR=#333333]Range("C14").Value <> ""[/COLOR][COLOR=#333333] Then prop.Value = Range("C14").Value[/COLOR]

 

[brentcook]

I ended up messing with the Sharepoint end. There is a section, since its a date, that asks:

Default value:

<tbody>
[TD="class: ms-authoringcontrols"]<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>

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>

[/TD]

</tbody>

 

[JamesAs]

Hi, so what was you final solution with this in the end?

 

[brentcook]

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.