7 Replies Latest reply on Nov 11, 2018 8:17 PM by Sayaka Etheridge

    Cannot link Number-type CustomProperty to Excel cell

    Sayaka Etheridge

      Hi, I am trying to link Custom File Properties to cells in Excel, and having a problem linking some number-type properties.

      Among other variables entered through EPDM datacard, I have two variables "RunMacro" and "ReqPWHT" that are entered through checkbox control.

      I tried linking these properties to named cells on my worksheet, but when I close and open the file, it reverts back to not linked (link never stays).

      Is it because the properties are in Number type? If anyone have any tips to link these properties, that would be appreciated. Thanks

       

        • Re: Cannot link Number-type CustomProperty to Excel cell
          Ronald Michaud

          Seems as there may be a few steps that you will need to add to your macro if you haven't already.

          You may need to add an instruction to delete that particular custom property and then set that custom property so that it rebuilds this each time the document is opened, especially if the check box is required each time.

           

          Delete custom property

          ActiveWorkbook.CustomDocumentProperties("Description").Delete

           

          Add custom property.

          ActiveWorkbook.CustomDocumentProperties.Add Name:="Description", LinkToContent:=True, Type:=msoPropertyTypeString, LinkSource:="Description"

           

          And then update the range.

          Range("Description").Value = ThisWorkbook.CustomDocumentProperties("Description")

           

          If you find that the "named range" has vanished then you may need to add instruction in your macro to name the range before adding the custom document property.

           

          There is much more to the macro of course that has to be defined but if you haven't added some instruction to rebuild the link then yes, the link can break.

           

          Ron

            • Re: Cannot link Number-type CustomProperty to Excel cell
              Sayaka Etheridge

              Hi Ronald,

              Thanks very much for your response. I have added the above code to Auto_Open macro and the link between the custom property and the named range are successfully rebuilt:) One problem I still have is, there is no value in the custom property added by this macro - I am assuming it is because value is deleted when the custom property is deleted. I tried adding the code to map the value to named range before deleting (as below), but then, I will have to copy the value in the named range back to newly created custom property, which I am not sure how to. Would you be able to lead me to the right direction?

               

              'Copy the value of custom property to named range before deleting

              Range("Description").Value = ThisWorkbook.CustomDocumentProperties("Description")

               

              'Delete custom property

              ActiveWorkbook.CustomDocumentProperties("Description").Delete

               

              'Add custom property.

              ActiveWorkbook.CustomDocumentProperties.Add Name:="Description", LinkToContent:=True, Type:=msoPropertyTypeString, LinkSource:="Description"

               

              'Update custom property to the original value

              ????

               

              Many thanks, Sayaka

                • Re: Cannot link Number-type CustomProperty to Excel cell
                  Ronald Michaud

                  Sure,

                  Deleting the custom property shouldn't delete the named range. Verify that this is happening first. If it is for some reason, try to figure out what instruction is causing this. If your Macro creates a new worksheet in it's process and deletes the sheet with the named ranges then you will need to add code to name the ranges on the new sheet.

                  There are a couple of ways to rename the range. If you have a fixed cell location then simply name the cell.

                  Range("A17").Name = "Description"

                  Another way is to reference another element in the document and use an offset.

                   

                  Dim description As String

                  description=Range ("Description").Value

                   

                  For Each cell In ActiveSheet.UsedRange.Cells

                      If cell.Value="Description" Then

                            cell.Offset(1, 0).Name=Description

                       End If

                  Next

                  The offset location (1, 0) will reference the cell below any cell that contains the word "Description" in it. If you want the offset location to be the cell to the right then simply change the reference to (0, 1). In this same manor you can also push .Value instead of .Name. Simply change the syntax after the offset like this

                   

                  cell.Offest(1, 0).Value=Description

                   

                  If you have more than one cell that you are doing this for then add the Dim _ As String for each, then in the If statement, for each new cell to be named add a new ElsIf statement.

                  Sub WriteNamedCells(myForm)

                      Dim franchise As String

                      Dim series As String

                      Dim description As String

                   

                     

                      franchise = Range("Franchise").Value

                      series = Range("Series").Value

                      description = Range("Description").Value

                   

                    

                  For Each cell In ActiveSheet.UsedRange.Cells

                          If cell.Value = "DESCRIPTION" Then

                              cell.Offset(0, 1).Value = description

                          ElseIf cell.Value = "COMPANY" Then

                              cell.Offset(0, 1).Value = franchise

                          ElseIf cell.Value = "MODEL" Then

                              cell.Offset(0, 1).Value = series

                          End If

                      Next

                   

                  It may also be of value to add code to prevent this from running every time the document is open. A simple If statement in front of the delete and add code will help if there is a part of the document that you can query.

                   

                  If Sheet1.Range("A17").Value = "Description" Then Exit Sub

                   

                  Ron

                    • Re: Cannot link Number-type CustomProperty to Excel cell
                      Ronald Michaud

                      Also, the  "Copy the value of custom property to named range" code can be moved to get the values from the PDM variables after the delete, rename and add code.

                      Ron

                      • Re: Cannot link Number-type CustomProperty to Excel cell
                        Sayaka Etheridge

                        Thanks again for your quick response.

                        Sorry I didn't explain myself very well, but the named range didn't get deleted and the link between the custom property and the named range were successfully rebuilt thanks to your code.

                        My problem is;

                        - When the variable is entered through check box control, it gives the value of "1" to the custom property.

                        - When the custom property is deleted by Macro, it also deletes the value "1" (my assumption)

                        - Newly created custom property by Macro doesn't have a value "1"

                        - Macro updates and links the custom property to the named range, but the original value "1" is not rebuilt

                         

                        I will give try and error with arrangement to the code you have given me. Thanks again for your kind assistance, much appreciated.

                          • Re: Cannot link Number-type CustomProperty to Excel cell
                            Ronald Michaud

                            Hi Sayaka,

                            I have just noticed an oversight on my part, unfortunately Link to Content is not allowed for that type of custom property. Only for text. You basically are looking for a mechanism to launch your macro and have decided to use a Boolean integer to accomplish this. You can and it will work, but it will not be linked to a named range. If you can't change this to a text type of custom property for your macro then you may have to do this another way. You can also write code to create that custom property type with a predefined value but it will always set that value to what you tell it to and won't allow user selection.

                            ActiveWorkbook.CustomDocumentProperties.Add Name:="Run Macro", LinkToContent:=False, Type:=msoPropertyTypeNumber, Value:="1"

                            Another option is to have your macro run from a different value that PDM can pass to your form for it to run automatically. There are several ways to accomplish this, it depends on what you are trying to produce and where you are trying to produce it.

                            I use "Folder Data Card Variables" in my Template Cards to set prerequisites that my macros use to perform form options when they create new documents. No checkbox or buttons required. It's very efficient and takes the guess work out of the user.

                            Another option is to not link your value to a named range, leave it as Boolean, and reference that in your macro for a go/no-go input. You would need to Dim as Boolean whatever that value is and construct your code accordingly.