PDA

View Full Version : Conditionally Populate Value in Rows Using List/ComboBox



Binod199
03-25-2009, 06:43 AM
Hi,
I am complete new to VBA/Excel but well versed with other programming language such as VB,VB.Net, C#.

At First i was totally blank where to code and what. Then i record MACRO and open Editor, gradually i was able to do something.

Still i have so many confusion whether i am doing right or wrong, because i dont have basic knowledge about VBA.

My requirement is some what like. Following are the Columns of sheet

Items Threat Effect Cure

There are around 20 Items. it should be in dropdown. Based on Item selection, Threat should be appear in next column. There are around 10 -15 threat against each Item. So all the dependent Threat should appear in each row of next column.

Then Against each Threat there are number of Effects. These effect should appar in single cell against each threat in next cell.

Finally there are some cures that should come in next column.

This way one record is complete.

There may be number of records. I have to automate these.

For Items in first Column i am using Tools--->Validateion---->List

Based on the Selected value in Cell i am copying contents to next column from another location. like this

If Range("A2").Value = "Application" Then
Range("L6:L10").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste

End If
If Range("A2").Value = "Communication" Then
Range("M6:M10").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste

To Add Effect in Next Column i m doing following

Range("C2").Select
Range("C2") = "AppThreat1Vul1 , AppThreat1Vul2, AppThreat1Vul3, AppThreat1Vul4, AppThreat1Vul5"
'Hard Coded Value, but have to read from sheet
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End If

I placed on button on the sheet. And Click of this button i have written all the code.

What is correct way to complete this requirement.

yours reply/Advice will be highly appreciated.

Thanks
Binod Kumar