locked
Need to use an apostrophe in VBA formula RRS feed

  • Question

  • Hello again,

    I almost have this project done but i am hitting a wall with this one. I need to use the apostrophe in the formula to denote the sheet named Projects as the source for the Data Validation list I am specifying. I will be using a userform so the user can select an option and based upon that, the correct data validation list will populate several cells. I have tried double quotes around Projects but that makes excel angry (doesnt work). The single apostrophe makes excel read the rest as a comment. Here is the formula I am trying to set:

    Range("E3").Formula = "=INDIRECT(" 'Projects'!P:P")"

    Thanks


    • Edited by JJG1118 Tuesday, July 10, 2012 1:32 PM
    Tuesday, July 10, 2012 1:31 PM

Answers

  • Let's say you have a text box or combo box ListChoice on your userform in which the user enters or selects the list to use, in this example FifteenList.

    Code to set validation:

        With Range("E3").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & Me.ListChoice
        End With


    Regards, Hans Vogelaar

    • Marked as answer by JJG1118 Tuesday, July 10, 2012 5:05 PM
    Tuesday, July 10, 2012 4:59 PM

All replies

  • Range("E3").Formula = "=INDIRECT(""'Projects'!P:P"")"

    Try above.But if you want to use as formula for List you shoul use a workbook level name.

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and Mark as Answer if it solves the issue.

    Tuesday, July 10, 2012 1:47 PM
    Answerer
  • The double quotes in the INDIRECT function are within the quoted string "=INDIRECT(...)". If you want to include quotes within a quoted string, you must double them. This tells Visual Basic that they are literal characters.

    Range("E3").Formula = "=INDIRECT(""'Projects'!P:P"")"


    Regards, Hans Vogelaar

    Tuesday, July 10, 2012 1:48 PM
  • Ok so how you both specified it allows it to run, however it only shows the first item listed in the P column. I am not sure why it is doing that.
    Tuesday, July 10, 2012 1:58 PM
  • Can you explain what you want to set data validation to?

    Regards, Hans Vogelaar

    Tuesday, July 10, 2012 2:42 PM
  • Hans,

    Thank you for your time. I want it to be a list using all values in the P column on the sheet called Projects. The specific amount of rows in that list will change (it will be updated from time to time). I want to just have the entire column used in the list so that others will not have to change that formula in the future. 

    Tuesday, July 10, 2012 2:46 PM
  • Create a named range MyList that refers to

    =OFFSET(Projects!$P$1,0,0,COUNTA(Projects!$P:$P),1)

    Set the Source for the data validation to

    =MyList

    The list will adjust itself dynamically to the number of filled cells in column P on the Projects sheet.


    Regards, Hans Vogelaar

    Tuesday, July 10, 2012 3:21 PM
  • I created the named range (I called it FifteenList) (that fits the overall structure of the program I have been working on much better). I have tried different vba formulas for placing data validation into a cell and none have worked. Can you help with that? How do I write the validation in vba using a named range for the source?
    Tuesday, July 10, 2012 4:40 PM
  • You'll have to provide much more detailed information if you want help with that.

    Regards, Hans Vogelaar

    Tuesday, July 10, 2012 4:43 PM
  • I want to have a specific cell, say E3, to have a data validation drop down list placed into it, using the list FifteenList as the source. I need this to be done using VBA because I will have sixteen other formulas that will be similar, using different lists as the source. The user will have a userform that they will select which formula they want populated in the cell and based on their selection, the appropriate validation list will be put into the cell. I have the userform ready, but I am stuck on how the write the data validation drop-down list in VBA.
    Tuesday, July 10, 2012 4:49 PM
  • Let's say you have a text box or combo box ListChoice on your userform in which the user enters or selects the list to use, in this example FifteenList.

    Code to set validation:

        With Range("E3").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & Me.ListChoice
        End With


    Regards, Hans Vogelaar

    • Marked as answer by JJG1118 Tuesday, July 10, 2012 5:05 PM
    Tuesday, July 10, 2012 4:59 PM
  • Hans, you are a lifesaver! It works like a charm. Thank you for your time and assistance!

    Cheers,

     
    Tuesday, July 10, 2012 5:06 PM