Microsoft Excel Forum

Forums

Microsoft Excel Forum
By Richard Rost

This forum is for the discussion of Microsoft Excel.

Click Here To Subscribe to this forum and receive an email update whenever new posts are added, just scroll down to the bottom of this page and enter your email address in the comment form.

Permanent Link
Keywords: microsoft excel forum

Post New Topic

Read Email Data Collection by Sandra M @ 1/26/2018

Read Move Row by Toine v @ 11/29/2017

Read Move Row by Toine v @ 11/16/2017

Read Go to Last First sheet by MUBEEZI M @ 7/12/2017

Read Purchase Question by ADAM S @ 5/2/2017

Read Excel Combobox by Eddy @ 1/1/2017

Read Macro by Richard L @ 11/28/2016

Read Macro by Richard L @ 11/26/2016

Read Macro by Richard L @ 11/23/2016

Read Range Method limit by Gary @ 11/18/2016

Alex,
I actually found a work around so I thought Id post what was going on incase anyone else runs into this problem.

I have a fairly large spreadsheet that has a bunch of validation dropdowns. Now whenever a new sheet is needed I use a macro to create the new sheet and apply all the validations.

Original Code shortened


    With Range("B13:B20,B22:B29,B31:B42,B44:").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Keys!$C$2:$C$31"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True   
    End With

As my spreadsheet grew this worked fine until I figured out that the range method has a 255 character limit. Now it took me a bit to figure this out, but to get around the 255 character limit you have to use a union to join your string into a variable then use the properties of the variable to apply the validations.


New Code Shortened
Set R1 = Range("E13:AJ20,E22:AJ29,")
Set R2 = Range("E160:AJ171,E176:AJ183")

Set RngAll = Application.Union(R1, R2)
   
    'Key Range
    With RngAll.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Keys!$A$2:$A$20"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True    
    End With

Reply from Alex Hedley:

Thanks for sharing.

Show Just This Thread Post Reply

Read Range Method limit by Gary @ 11/15/2016

Read Excel VBA by Robert Wathen @ 10/10/2016

Read Excel LOOKUP function example by Ron @ 7/24/2016

Read Help with formula by Bruce @ 5/27/2016

Read Green Triangle by Bruce @ 5/27/2016

Read Help with formula by MICHAEL J @ 5/17/2016

Read VLookup to return multiple values by Anthony P M @ 2/2/2016

Read PowerPivot in Excel 2016 by Bruce Reynolds @ 1/30/2016

Read PowerPivot in Excel 2016 by Bruce Reynolds @ 1/28/2016

Show Older Posts Expand All Topics

Post Your Comments or Subscribe
If you would like to be notified of new posts on this forum,
just enter your email address below. It will be kept private.

If you just want to subscribe to get email updates when this forum is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
Your Name:	Required
Your Email:	NOT Public
Subject:
Comments:
	Sorry about this step. It's just to keep the spam bots away:
Verify:	What is 8+2:

	Notify me when the this forum is updated
	Remember Me for my next comments


Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead. I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be. As always, I promise to never give away your personal information to anyone else, ever. NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard