Hi experts,

I have a minor problem....

In column D i have a data validation list in each cell.
Until now, I had only 100 lines in my application. The size of the
application was about 300kb

Because i do not know how many lines the user will put in, i have made all
cells ranging from D4:D65000 with datavalidation.

Now my application is about 1.700kb
Is there a way to only have a data validation (dropdownlist) in column D when
the user puts something in column B on the same line ?

How would i program that in VBA ?

Thanks,
Pierre

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200601/1

Hi Pierre,
Right click the SheetTabView Code then copy this into that Sheet's
Code Module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column < 4 Then Exit Sub
Range("D1").Copy
ActiveCell.PasteSpecial Paste:=xlPasteAll
End Sub

Ken Johnson

Hi Ken,

Thanks for your input but i do not understand completely what happens...
can you give me an explanation line by line please?
Thanks,
Pierre

Ken Johnson wrote:
Hi Pierre,
Right click the SheetTabView Code then copy this into that Sheet's
Code Module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column < 4 Then Exit Sub
Range("D1").Copy
ActiveCell.PasteSpecial Paste:=xlPasteAll
End Sub

Ken Johnson

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200601/1

Hi Pierre,
After re-reading your post I am not too sure that I fully understand
what you are trying to do.

Is there a way to only have a data validation (dropdownlist) in column D when
the user puts something in column B on the same line ?

I can't figure out what column B has to do with the validation.
I thought that when the user selects a cell in column D that you wanted
a dropdown list to appear programatically so that you don't have to
waste memory on data validation in the whole of column D. This is what
my suggested code will do if D1 has the appropriate data validation.

Ken Johnson

Hi Pierre,
OK, line by line...
First, do you have a problem with where this code has to be pasted?
Ken Johnson

Hi Pierre,
To speed things up a bit I'll explain where the code has to be and why,
but you probably already know this.
The code is an event procedure which runs every time the user changes
which cell is selected on the sheet. You only want this code to run
when the selection change occurs on the sheet with the data validation
you are using. Because of this the code should be pasted into the code
module of the sheet with the data validation. The quickest way to get
to that module is to right-click that sheet's sheet tab (towards the
bottom of the screen), then you will see a popup with "View Code" at
the bottom. Select "View Code" and that Sheet's code module is the
large white space to the right. Assuming you had previously copied the
code (all five lines) you then just paste it into the code module.

The first line of the code checks to see if the user clicked anywhere
in column D. Column D is the fourth column, hence the <4. If the user
selected a cell in any other column then the Sub Procedure is exited.
If the user selects a column D cell then the code copies cell D1, where
you have already added the data validation.
The next line of code pastes D1 into the cell that the user selected,
and that includes the data validation, because of the xlPasteAll.
Here is where I have to make a confession Pierre. When I saw your
"minor problem" I thought to myself, "that can't be too difficult" and
I set about using the macro recorder to see what sort of code to use.
However, when I used a variation of the code produced by the macro
recorder I kept on getting an error. Not so easy after all!
Much to my surprise, the recorder used "Paste:=xlDataValidation",
which seems logical, but it is responsible for the error that I kept on
getting, and I could only avoid that error by using
"Paste:=xlPasteAll" in its place. Curiously the value in D1 does
not appear, but the validation dropdown list does!

Hope this clears everything up and that the code is what you're after
to solve your "minor problem".

Ken Johnson

Hi Pierre,
Here I go again!
I really am a sloppy reader!
I just noticed that your validation doesn't start until D4, not D1 as I
was using.
And, I was wrong about the value of the copied cell not being pasted.
It is pasted and that is disasterous when the user clicks on a column D
cell that has previously been set! That value is then lost.
To solve these "big problems" the code would have to be changed to...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column < 4 _
Or ActiveCell.Row < 5 Then Exit Sub
Dim OriginalValue
OriginalValue = ActiveCell.Value
Range("D4").Copy
ActiveCell.PasteSpecial Paste:=xlPasteAll
ActiveCell.Value = OriginalValue
End Sub

Now, the Sub is exited when the user clicks anywhere other than inside
the range D4:D65536.
The value of the selected cell is remembered by the variant variable
called OriginalValue.
D4 is copied and pasted into the selected cell, which includes the data
validation, then the OriginalValue is put back into the selected cell
(which most times will just be nothing).

I hope no more problems arise (but I wouldn't bet on it!)

Ken Johnson

Hi Ken,

hm...not so easy afterall indeed !

Youre code works fine but when a users has already (somewhere in the past)
changed for example line 5 and in column D it says "closed", this is
overwritten when the users changes column 4 again....

Is there a way to avoid thsi so that a given data from the validation list
will not be overwritten unless the users uses the dropdownlist again?

Thanks,
Pierre

Ken Johnson wrote:
Hi Pierre,
To speed things up a bit I'll explain where the code has to be and why,
but you probably already know this.
The code is an event procedure which runs every time the user changes
which cell is selected on the sheet. You only want this code to run
when the selection change occurs on the sheet with the data validation
you are using. Because of this the code should be pasted into the code
module of the sheet with the data validation. The quickest way to get
to that module is to right-click that sheet's sheet tab (towards the
bottom of the screen), then you will see a popup with "View Code" at
the bottom. Select "View Code" and that Sheet's code module is the
large white space to the right. Assuming you had previously copied the
code (all five lines) you then just paste it into the code module.

The first line of the code checks to see if the user clicked anywhere
in column D. Column D is the fourth column, hence the <4. If the user
selected a cell in any other column then the Sub Procedure is exited.
If the user selects a column D cell then the code copies cell D1, where
you have already added the data validation.
The next line of code pastes D1 into the cell that the user selected,
and that includes the data validation, because of the xlPasteAll.
Here is where I have to make a confession Pierre. When I saw your
"minor problem" I thought to myself, "that can't be too difficult" and
I set about using the macro recorder to see what sort of code to use.
However, when I used a variation of the code produced by the macro
recorder I kept on getting an error. Not so easy after all!
Much to my surprise, the recorder used "Paste:=xlDataValidation",
which seems logical, but it is responsible for the error that I kept on
getting, and I could only avoid that error by using
"Paste:=xlPasteAll" in its place. Curiously the value in D1 does
not appear, but the validation dropdown list does!

Hope this clears everything up and that the code is what you're after
to solve your "minor problem".

Ken Johnson

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200601/1

Hi Pierre,
my replies seem to be a little delayed.
I've spotted and fixed the problem of overwritten and lost old values.
I now just want to add two little improvements...
One trivial change that stops the appearance and disappearance of
values in the selected cell. If the selected cell was empty the D4
value momentarily appears. This is stopped by adding the line
"Application.ScreenUpdating = False". It also speeds things up a bit.
The second change gets rid of the "marching ants" that appear around
the copied D4 cell.
"Application.CutCopyMode = False" gets rid of those...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If ActiveCell.Column < 4 _
Or ActiveCell.Row < 5 Then Exit Sub
Dim OriginalValue
OriginalValue = ActiveCell.Value
Range("D4").Copy
ActiveCell.PasteSpecial Paste:=xlPasteAll
ActiveCell.Value = OriginalValue
Application.CutCopyMode = False
End Sub

That should do it.

Ken Johnson

Thanks Ken !
i am learning every day !
Pierre

Ken Johnson wrote:
Hi Pierre,
my replies seem to be a little delayed.
I've spotted and fixed the problem of overwritten and lost old values.
I now just want to add two little improvements...
One trivial change that stops the appearance and disappearance of
values in the selected cell. If the selected cell was empty the D4
value momentarily appears. This is stopped by adding the line
"Application.ScreenUpdating = False". It also speeds things up a bit.
The second change gets rid of the "marching ants" that appear around
the copied D4 cell.
"Application.CutCopyMode = False" gets rid of those...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If ActiveCell.Column < 4 _
Or ActiveCell.Row < 5 Then Exit Sub
Dim OriginalValue
OriginalValue = ActiveCell.Value
Range("D4").Copy
ActiveCell.PasteSpecial Paste:=xlPasteAll
ActiveCell.Value = OriginalValue
Application.CutCopyMode = False
End Sub

That should do it.

Ken Johnson

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200601/1

Hi Pierre,
You're welcome, glad I could help.
I too am learning everyday!
Ken Johnson