using macro, how can i assign list box (having values "yes" or "no" )
to all the fields of column D

Assuming you mean "cells" in your post, where you say "fields".

Do you *need* to perform this via macro? It's certainly very easy to
perform the operation once manually, and copy paste it into other
fields.

If you need to perform this via macro, you could record the manual
operation described above.

How about selecting your range and then data|validation.

stats wrote:

using macro, how can i assign list box (having values "yes" or "no" )
to all the fields of column D

--

Dave Peterson

This is what i am looking

i want to create a macro, as soon as i open the spreadsheet, it should
run the macro automatically (which i guess can be done using Auto_Open)

the macro should behave as follows
1) if value in column A is of the format 2.2 (integer.integer), then
fill the column C of the same row with list box (containing "yes" or
"no")

2) if value in column A is of the format 2.2 (integer.integer), and
value of column C is "some string" then fill the column C with same
value (preserve the data if it already exists)

I guess i can't do this manually if i have more than 1000 rows.

If you select the whole range, you can do it all at once.



stats wrote:

This is what i am looking

i want to create a macro, as soon as i open the spreadsheet, it should
run the macro automatically (which i guess can be done using Auto_Open)

the macro should behave as follows
1) if value in column A is of the format 2.2 (integer.integer), then
fill the column C of the same row with list box (containing "yes" or
"no")

2) if value in column A is of the format 2.2 (integer.integer), and
value of column C is "some string" then fill the column C with same
value (preserve the data if it already exists)

I guess i can't do this manually if i have more than 1000 rows.

--

Dave Peterson

Yeah, but I would liek to create a macro, as you see I have couple of
conditions to be satisfied before assigning..

is this doable using macro???

This might help.

Option Explicit
Sub auto_open()

Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("sheet1").Range("a1:A1000")

For Each myCell In myRng.Cells
With myCell
.Offset(0, 2).Validation.Delete
If IsNumeric(.Value) _
And .Text Like "#.#" Then
With .Offset(0, 2)
.Value = ""
With .Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End With
End If
End With
Next myCell
End Sub



stats wrote:

Yeah, but I would liek to create a macro, as you see I have couple of
conditions to be satisfied before assigning..

is this doable using macro???

--

Dave Peterson

Hey Dave, can u please explain the code in brief..i m new to VBA...

Intermingled.


Option Explicit
Sub auto_open()

'define some variables
Dim myRng As Range
Dim myCell As Range

'what cells to look at. Change the worksheet name
'and the address to what you need
Set myRng = Worksheets("sheet1").Range("a1:A1000")

'look at each cell in that range
For Each myCell In myRng.Cells
With myCell
'offset(0,2) is two columns to the right
'since mycell is in column A, then .offset(0,2) is in column C
'remove any existing validation
.Offset(0, 2).Validation.Delete
If IsNumeric(.Value) _
And .Text Like "#.#" Then
'if it's a number that looks like 2.2 (or 3.6 or 7.9 or 8.0)
With .Offset(0, 2)
'clear out the value in Column C
.Value = ""
With .Validation
'add the Yes/No validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End With
else
'do nothing
'just leave column C's value alone.
End If
End With
Next myCell
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

stats wrote:

Hey Dave, can u please explain the code in brief..i m new to VBA...

--

Dave Peterson

Hey david,

it is not preserving the value of column C. If the valus already exists
it should preserver the data instead of creating list box

Hey David,

you are clearing the column C and adding the validation

i want to add validation only if column C is empty

i think so, this will work out

For Each myCell In myRng.Cells
With myCell
.Offset(0, 2).Validation.Delete
If IsNumeric(.Value) _
And .Text Like "#.#" Then
With .Offset(0, 2)
If IsEmpty(.Value) Then
.Value = ""
With .Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"

End With
End If
End With
End If
End With
Next myCell

Next

Hey David,

is it possible to set the color, I mean if user selects yes the color
of cell should change to red
if no is selectd then blac etc

Hey david, i guess i can do this to change color

If IsEmpty(.Value) Then
.Value = ""
.Interior.ColorIndex = 34


But there is one issue with macro, once i select the cell, select yes
and when i again click on the same cell, i do not get the drop down
box...the value gets fixed to yes...this should not happen

There's nothing in that code that removes the data|validation after it's
applied.

stats wrote:

Hey david, i guess i can do this to change color

If IsEmpty(.Value) Then
.Value = ""
.Interior.ColorIndex = 34

But there is one issue with macro, once i select the cell, select yes
and when i again click on the same cell, i do not get the drop down
box...the value gets fixed to yes...this should not happen

--

Dave Peterson

hey david, this works fine for integer of type 2.2
And .Text Like "#.#" Then
'if it's a number that looks like 2.2 (or 3.6 or 7.9 or
8.0)

what to do for 2.2.2.2
i tried #.#.#.# it didn't worekd

Please ignore my previous question...

the only thing is how can i change the cell color based on selection
from list box