Anthony's VBA Forum Index Anthony's VBA Forum
Where the knowledge is shared
Menu
 Anthony's VBA Forum IndexHome Page
 Anthony's VBA Forum IndexForum Index
FAQFAQ
MemberlistMemberlist
UsergroupsUsergroups
RegisterRegister
ProfileProfile
Log in to check your private messagesMessages
Log inLogin/Out

Quick Search

Advanced Search

Links
Consulting
Products

Who's Online
[ Administrator ]
[ Moderator ]


Conditionally Populate Value in Rows Using List/ComboBox

 
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA
View previous topic :: View next topic  
Author Message
Binod199



Joined: 25 Mar 2009
Posts: 1

PostPosted: Wed Mar 25, 2009 9:37 am    Post subject: Conditionally Populate Value in Rows Using List/ComboBox Reply with quote

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
View user's profile Send private message
Kenneth Hobson
Gold Member
Gold Member


Joined: 25 Mar 2007
Posts: 21
Location: Tecumseh, OK

PostPosted: Fri Apr 24, 2009 11:14 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA All times are GMT - 4 Hours
Page 1 of 1

 
Jump to:  
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