Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Stock Macro Question
Hi,
Thank you to all who replies to my previous post. It seems I had misunderstood the problem. (Going back to my BMX bike example) We have the following data: ProdID Desc Price 2374 BMX Bicycle £99.99 Now this bike comes in various colours (Red Green and Blue), so what I want to be able to do, is slect the entire row, and run a macro. Which would prompt the user to specify what colours the product was available in. So for this example, the user wouldd type "R,G,B". Which would then replace to current line, with the following: ProdID Desc Price 2374/R BMX Bicycle £99.99 2374/G BMX Bicycle £99.99 2374/B BMX Bicycle £99.99 Does anyone know how I would acomplish this? Kind Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Stock Macro Question
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "2:20" '<== change to suit Dim ans, aryOptions Dim i As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target ans = InputBox("What colours?") If ans = False Then Exit Sub aryOptions = Split(ans, ",") Target.Offset(1, 0).Resize(UBound(aryOptions) - LBound(aryOptions)).EntireRow.Insert Target.EntireRow.AutoFill Target.Resize(UBound(aryOptions) - LBound(aryOptions) + 1).EntireRow For i = LBound(aryOptions) To UBound(aryOptions) Me.Cells(Target.Offset(i, 0).Row, "A").Value = Me.Cells(Target.Offset(i, 0).Row, "A").Value & "\" & aryOptions(i) Me.Cells(Target.Offset(i, 0).Row, "D").Value = Me.Cells(Target.Row, "D").Value Next i End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Materialised" wrote in message ... Hi, Thank you to all who replies to my previous post. It seems I had misunderstood the problem. (Going back to my BMX bike example) We have the following data: ProdID Desc Price 2374 BMX Bicycle £99.99 Now this bike comes in various colours (Red Green and Blue), so what I want to be able to do, is slect the entire row, and run a macro. Which would prompt the user to specify what colours the product was available in. So for this example, the user wouldd type "R,G,B". Which would then replace to current line, with the following: ProdID Desc Price 2374/R BMX Bicycle £99.99 2374/G BMX Bicycle £99.99 2374/B BMX Bicycle £99.99 Does anyone know how I would acomplish this? Kind Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Stock Macro Question
Bob Phillips wrote:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "2:20" '<== change to suit Dim ans, aryOptions Dim i As Long On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target ans = InputBox("What colours?") If ans = False Then Exit Sub aryOptions = Split(ans, ",") Target.Offset(1, 0).Resize(UBound(aryOptions) - LBound(aryOptions)).EntireRow.Insert Target.EntireRow.AutoFill Target.Resize(UBound(aryOptions) - LBound(aryOptions) + 1).EntireRow For i = LBound(aryOptions) To UBound(aryOptions) Me.Cells(Target.Offset(i, 0).Row, "A").Value = Me.Cells(Target.Offset(i, 0).Row, "A").Value & "\" & aryOptions(i) Me.Cells(Target.Offset(i, 0).Row, "D").Value = Me.Cells(Target.Row, "D").Value Next i End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Thanks Bob, that worked great. As so often happens, my manager has now decided he would like to do it another way. (Going Back to my BMX example) Colour Codes ProdID Desc Price 2374 BMX Bicycle £99.99 A new row has been inserted, where the user will type the various colour codes into. So for example: Colour Codes ProdID Desc Price W,R,B 2374 BMX Bicycle £99.99 The user would then select this row, and run a macro, and would have: Colour Codes ProdID Desc Price W,R,B 2374/W BMX Bicycle £99.99 W,R,B 2374/R BMX Bicycle £99.99 W,R,B 2374/B BMX Bicycle £99.99 I would imagine the pincipal is very similar to what you earlier presented me with. But to be totally honest, I am no windows programmer, Im a UNIX admin, and its been over 10 years since I even made a ripple in the water with any windows programming. So any advise or pointers you could give me would great. Thanks Mick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stock control question | Excel Worksheet Functions | |||
stock control question | New Users to Excel | |||
How many days the ready stock+expected stock will last as allocate | Excel Discussion (Misc queries) | |||
Stock control formulas, counting and reporting remaining stock | Excel Programming | |||
Office 2003 Stock Action Add-In - Stock Handling Capacity/Numbers | Excel Worksheet Functions |