Input Box Macro Help

[Chris]

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.

 

[Mike H]

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

 

[Shasur]

Hi Chris

You can try the following code in the BeforeDoubleClick of the required
worksheet

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim sVal
Dim oRange As Range
Dim i As Long


If Target.Address = Range("A8").Address Then
sVal = InputBox("how many packs have you issued for Astute?")
If Len(sVal) <> 0 Then
' Deduct Values in C
For i = 4 To 9
Cells(i, 3).Value = Cells(i, 3).Value - Val(sVal)
Next i
End If

'Add value to corresponding range
Set oRange = Range("F4")
oRange.Offset(0, Month(Now)).Value = oRange.Offset(0,
Month(Now)).Value + sVal


End If
End Sub

 

[Per Jessen]

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]

Thanks for all your excellent help Mike H - very much appreciated.

Cheers,

Chris.

 

[Chris]

Thank you Shasur for all your wonderful help - code works well - many
thanks,

Cheers,

Chris.

 

[Chris]

Hi Per Jessen, thank you for your fabulous assistance - really
appreciate your help,

Cheers,

Chris.