Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Macro Help
Hello, could someone please help me create a macro to do the following:
When the user double-clicks on cell A8 then an Input Box appears asking the user "how many packs have you issued for Astute?" The user enters in a number (greater than or equal to one) and clicks OK to proceed. The number entered in needs to automatically deduct from the the number that exists in the following cells: C4, C5, C6, C7, C8 and C9. Column C is the SOH column (Stock on Hand). Column A is the Product Name column. Also, the number entered needs to automatically be added to whatever number exists in the current month's cell: MONTH CELL LOCATIONS: G4 for Jan H4 for Feb I4 for Mar J4 for Apr K4 for May L4 for Jun M4 for Jul N4 for Aug O4 for Sep P4 for Oct Q4 for Nov R4 for Dec For example: If the user enters in the number 400 into the Input Box, then quantity 400 needs to automatically be deducted from whatever number that exists in cells: C4, C5, C6, C7, C8 and C9. Also, as the current month is Feburary, then 400 needs to be added to whatever number is currently in cell: H4. The workbook is named: Packs.xls The worksheet is named: Product SOH FY 08-09 I am using Excel 2003. Any help would be greatly appreciated, Thanks, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Macro Help
Chris,
Right click your sheet tab, view code and paste this in and double click A8. You may want to consider adding some error trapping. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address < "$A$8" Then Exit Sub response = Int(Val(InputBox("how many packs have you issued for Astute?", "Astute"))) For Each c In Range("C4:C9") c.NumberFormat = "General" c.Value = Val(c.Value) - response Next Cells(4, 6 + Month(Now)).Value = Cells(4, 6 + Month(Now)).Value + response End Sub Mike "Chris" wrote: Hello, could someone please help me create a macro to do the following: When the user double-clicks on cell A8 then an Input Box appears asking the user "how many packs have you issued for Astute?" The user enters in a number (greater than or equal to one) and clicks OK to proceed. The number entered in needs to automatically deduct from the the number that exists in the following cells: C4, C5, C6, C7, C8 and C9. Column C is the SOH column (Stock on Hand). Column A is the Product Name column. Also, the number entered needs to automatically be added to whatever number exists in the current month's cell: MONTH CELL LOCATIONS: G4 for Jan H4 for Feb I4 for Mar J4 for Apr K4 for May L4 for Jun M4 for Jul N4 for Aug O4 for Sep P4 for Oct Q4 for Nov R4 for Dec For example: If the user enters in the number 400 into the Input Box, then quantity 400 needs to automatically be deducted from whatever number that exists in cells: C4, C5, C6, C7, C8 and C9. Also, as the current month is Feburary, then 400 needs to be added to whatever number is currently in cell: H4. The workbook is named: Packs.xls The worksheet is named: Product SOH FY 08-09 I am using Excel 2003. Any help would be greatly appreciated, Thanks, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Macro Help
Hi Chris
As this is an event code is has to be pasted into the code sheet for ThisWorkbook. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set January = Range("G4") MyMonth = Month(Date) If Target.Address = "$A$8" And ActiveSheet.Name = "Product SOH FY 08-09" Then Do Astute = InputBox("How maby packs have you issued for Astute?") Loop Until Astute = 1 For r = 4 To 9 Cells(r, "C").Value = Cells(r, "C").Value - Astute Next January.Offset(0, MyMonth - 1) = January.Offset(0, MyMonth - 1) + Astute End If Range("A2").select End Sub Best regards, Per "Chris" skrev i meddelelsen ... Hello, could someone please help me create a macro to do the following: When the user double-clicks on cell A8 then an Input Box appears asking the user "how many packs have you issued for Astute?" The user enters in a number (greater than or equal to one) and clicks OK to proceed. The number entered in needs to automatically deduct from the the number that exists in the following cells: C4, C5, C6, C7, C8 and C9. Column C is the SOH column (Stock on Hand). Column A is the Product Name column. Also, the number entered needs to automatically be added to whatever number exists in the current month's cell: MONTH CELL LOCATIONS: G4 for Jan H4 for Feb I4 for Mar J4 for Apr K4 for May L4 for Jun M4 for Jul N4 for Aug O4 for Sep P4 for Oct Q4 for Nov R4 for Dec For example: If the user enters in the number 400 into the Input Box, then quantity 400 needs to automatically be deducted from whatever number that exists in cells: C4, C5, C6, C7, C8 and C9. Also, as the current month is Feburary, then 400 needs to be added to whatever number is currently in cell: H4. The workbook is named: Packs.xls The worksheet is named: Product SOH FY 08-09 I am using Excel 2003. Any help would be greatly appreciated, Thanks, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Macro Help
Thanks for all your excellent help Mike H - very much appreciated.
Cheers, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Macro Help
Hi Per Jessen, thank you for your fabulous assistance - really
appreciate your help, Cheers, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run macro with input msg based on cell input | Excel Discussion (Misc queries) | |||
Input Box within a macro | Excel Discussion (Misc queries) | |||
Getting input within macro without the input box | Excel Programming | |||
input box within macro | Excel Worksheet Functions | |||
Macro asking for input | Excel Programming |