|
Anthony's VBA Forum Where the knowledge is shared |
|
|
View previous topic :: View next topic |
Author |
Message |
Binod199
Joined: 25 Mar 2009 Posts: 1
|
Posted: Wed Mar 25, 2009 9:37 am Post subject: Conditionally Populate Value in Rows Using List/ComboBox |
|
|
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
[b]
[u]Items Threat Effect Cure[/u][/b]
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 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"
[b]'Hard Coded Value, but have to read from sheet[/b]
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 put command 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 |
|
Back to top |
|
|
Kenneth Hobson Gold Member
Joined: 25 Mar 2007 Posts: 21 Location: Tecumseh, OK
|
Posted: Fri Apr 24, 2009 11:14 am Post subject: |
|
|
Change:
[code]
If Range("A2").Value = "Application" Then
Range("L6:L10").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste [/code]
To:
[code]
If Range("A2").Value = "Application" then _
Range("L6:L10").Copy Range("B2")
Application.CutCopyMode = False
[/code]
So, if your code is in a Module and the Sub is called DoItNow add the commandbutton control from the Control Toolbox toolbar, doubleclick it and add it. e.g.
[code]
Private Sub CommandButton1_Click()
DoItNow
End Sub
[/code]
Of course you could just put the code in the button's Sub if you did not need to play it by Alt+F8 or reuse it. Right click the button to edit the caption text. Click the first button on the Control Toolbox toolbar to toggle design mode for the controls. A click will then execute the Click event for that button.
This is a good site to go beyond the macro recorder. http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/
This is a good site to get started with macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm |
|
Back to top |
|
|
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2002 phpBB Group
|