Data Validation from Variable Source List Range

C

coggo

New Member
Joined
Jun 3, 2002
Messages
21
G'day,

Within a sreadsheet I have created, much data is validated by a list that is sourced from cells within the same spreadsheet (with an in-cell drop-down menu). What I am attempting to do is allow the user to, if necessary, add further items to the validation source list as required. This is simply a matter of entering a value into a cell in my spreadsheet and altering the source range for validation to include this new data. I am in the process of writing a macro to semi-automate this task by acquiring the user's desired addition to the source list via a message box and then adding it to the source list in a cell, and then finally updating the data validation settings to extend the source range for the cells to be validated. Effectively what is happening is that each time a new addition is made, the range of the source is increasing. My problem occurs though, in that I don't know how to make the source range "variable". An example of the code used to enter in the source of the validation list in VBA is:
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$S$4258:$S$4268"

Where Formula1: quotes the source range.

I would like to make the range variable (to allow for additions as the list increases), so that the second co-ordinate in this formula is a variable one. Within my macro, I have a variable that describes the total length of the source list but this cannot be entered into the formula describing the source range (Formula1), since it is obviously not constant! However, when I have tried to insert my variable into this formula, I only get errors. Is there any way that I might be able to get around this problem so that I can describe a variable source range?

I hope I have provided enough information, as this is my first post. Any help that anyone could provide would be greatly appreciated!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Hi,

Here is one option that you can try...

1. Name your source range, and make it dynamic. I called mine 'MyRange'

Insert>Name>
Names: MyRange
Refers to: =OFFSET(Sheet1!$S$4258,0,0,COUNTA(Sheet1!$S$4258:$S$65536),1)

2. In your cell data validation list, the formula becomes =MyRange.

As entries are added/deleted to MyRange, the data validation list automatically updates

You can also do the entire thing in code...

Code: 
Sub test()
    
 With ActiveWorkbook
 .Names.Add Name:="MyRange", RefersToR1C1:= _
        "=OFFSET(Sheet1!R4258C19,0,0,COUNTA(Sheet1!R4258C19:R65536C19),1)"
    With ActiveCell.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=MyRange"
    End With
End With
End Sub

Bye,
Jay
 
Upvote 0
If you name the source list using the Insert|Name|Define names function or the name box, then refer your data validation to the name, you can insert new variables in the list and the validation will include them. Can't add onto the bottom, but it should work if you insert your new entries somewhere in the list.
HTH
Richard
 
Upvote 0
Thansk for replying RichardS and Jay Petrulis! I really appreciate you getting back to me. I have now used Jay's advice and my macro is a success! Thanks again!
 
Upvote 0
I'm trying to do something similar to what was explained in this thread. The difference is what I need to do is really much more simple, I don't even need to dynamically resize the source range. Basically, I need the validation box to have as its source array a non-contiguous range of cell values. I couldn't get the validation list to take the non-contiguous range when entered in the formula1:= area, so I figured a named range would do the trick. However, I get an error at the validition.add line. Am I missing something here?


Code: 
Private Sub FillPickConditionFields(sh As Worksheet)
    sh.Cells.Validation.Delete
    ActiveWorkbook.Names.Add Name:="srtRng", RefersTo:="=Blur sheet!$A$4,Blur sheet!$E$4:$I$4"
    With sh.Range("C1").Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=srtRng"
        .InCellDropdown = True
    End With
End Sub


Thanks for any help,
Arthur
 
Upvote 0
Ok, I really hate to bump my post again. I simply cannot figure out what is going on here. Can anyone offer me their help?
 
Upvote 0
arthur

The thread you are in was started more than 4 years ago.

Perhaps you could start your own thread?

If you think it's needed put a link to this one in your post.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Source Formatting from a Data Validation
2
Replies
10
Views
239
CantExcel123
C
E
  • Question
Data Validation Source for dropdown cell based on variable range?
Replies
2
Views
84
EssKayKay
E
MartinS
  • Solved
Update Validation in Merged Cells
2
Replies
10
Views
126
Joe4
Joe4
Yecart77
  • Solved
Create Data Validation Fixed Range but remove blanks
Replies
3
Views
253
awoohaw
awoohaw
N
  • Solved
VBA Question
Replies
3
Views
126
RoryA
RoryA

Forum statistics

Threads
1,214,987
Messages
6,122,618
Members
449,092
Latest member
amyap

Share this page

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