Hi,
let's say I have created a validation list in cell A1, and list is called "My list". There are numbers from 1 to 9 in that list, and first cell in range of list is blank - so index of that cell would be 0.
Then I select something else than first - blank - item from list (numbers).
How can I, with VBA, change drop-down list index back to first (blank) cell ?
Tried with this, not working :
Please Login or Register to view this content.
Thanks for help !!
Is "My list" a named range of cells? If yes, the name wouldn't have a space in it. Otherwise, I'm not sure what you're asking.
You could reset A1 to blank.
Cells(1, 1).Value = ""
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
Yes, "My list" is a named range of cells. It is only example, you can name It My_List.
I'm trying to get all my drop-down cells blank, but need that in a range where there are other cells which have formulas in It, and formulas must be intact.
Sois not an option, because in range every cells would be blank.Cells(1, 1).Value = ""
Also, I forgot to mention - in range where I need that ("F18:BN412"), I have multiple drop-downs with different ranged names.
Last edited by Lukael; 07-30-2014 at 08:22 AM.
That doesn't make any sense to me. In order to reset any DV cells you must be able to identify them as DV cells, at which point you can simply set the value as AlphaFrog showed.
Everyone who confuses correlation and causation ends up dead.
Hi Lukael,
try semicolon
Please Login or Register to view this content.
Hi Nilem,
I tried, same result as in post#1
Ok, I understand you, but can this be done ?In order to reset any DV cells you must be able to identify them as DV cells
I know It sounds a little strange question, but I have huge table where there needs to be cleared all cells after running macro. And this range contains drop-down Shapes, DV cells and cells with formulas, which mustn't be deleted.
See this sample, open It and just hit the RUN button. After creating all months be focused on 1st day of months (excluding December which works???), you'll notice that It clears Drop-down shapes, leave formula cells intact but not values in DV cells.
The code that should clear cells is in Clear_cells macro....
Last edited by Lukael; 07-30-2014 at 08:53 AM.
You can use:
to clear the DV cells.Please Login or Register to view this content.
"It clears Drop-down shapes, leave formula cells intact but not values in DV cells."
maybe so
Please Login or Register to view this content.
Have tried, but not working.
Paste this code in sample and you'll see. Sample code doesn't properly clear Drop-down Shapes, this one does.
Please Login or Register to view this content.
I think the confusion has been because your terminology is incorrect. You are using Form-type dropdown lists and not Data Validation lists.
nilem suggestion should work.
Tried that too, still no succes :
I also called the code separately right under the code for Drop-Down shapes, same result.Please Login or Register to view this content.
I wasn't aware of that, I thought these are Validation lists since you need to asiggn them named range in Data>Validation>Settings>List. I'll read more about this !I think the confusion has been because your terminology is incorrect. You are using Forms-type dropdown lists and not Data Validation lists.
The code I posted clears data validation cells, not cells linked to Forms controls. (You have both)
Ok, now I'm really confused about this Data validation term. I pasted your code in this sample, but I still don't get 0 index in DV cell. I placed button for your code.
You're right ! Except that Form control has a list, and It's selected Item is inserted into cells under, which have also drop-down list in case User selects other values for Start/End times. Form control + these cells needs to be blank.The code I posted clears data validation cells, not cells linked to Forms controls. (You have both)
Not sure what you mean. In point of fact, since your DV cells only contain literal values, your Specialcells line that clears constants should clear those DV cells too, if they are populated.Originally Posted by Lukael
Yes, I know that, and I'm checking all the code If something prevents that clearing specialcells to be done, but can't figure It out. It worked 14 days ago, now It doesn't.
I figured It out !
Looks like there is some flaw in this lines of code :
I deleted It and tried only with :Please Login or Register to view this content.
Now It works, but executing of code is a little bit slower, so I reckon that Clear_cells macro has some errors. Is It better to separate all other commands in the macro and just call them within ?Please Login or Register to view this content.
Also, still can't figure why that lines of code would cause problem. All It does differently is how It determines where to clear special cells
I missed that - it should be:
Note the dots before CellsPlease Login or Register to view this content.
WHAT A STUPID mistake, wouldn't notice that EVER ! Thanks a lot !!! Now It works !!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks