Multi macro inputs

[LiAD]

Hi,

I have four macros on two different excel sheets all controlling a value in
the same cell. On sheet 1 I have two macros which either add or subtract 1
from cell I38 every time the button is pushed. On sheet 2 I have another two
macros which do the same thing - they add and subtract 1 from cell L6.

I need cell L6 from sheet 2 to update the value in sheet 1 cell I38 then
reset to zero, but maintain the value in cell I38 sheet 1.

Example:
- I hit +1 five times and -1 twice on sheet 1. My stock, (cell I38 sheet
1), goes from 20 to 23.
- I then hit +1 ten times and -1 once on sheet 2. Cell L6 on sheet 2 will
show that I have entered a total of 9 times.
- Cell L6 will then update cell I38 on sheet 1 from 23 to 32.
- Cell L6 sheet 2 will reset to zero and cell I38 sheet 1 will stay at 32
until the next update arrives.

Is this possible? I have attached the +1 macro i am using below for
reference - maybe i need something different to do all the operations I don't
know.

Thanks for your help

Sub Production15()
With Sheet2 ' Changer to suit
If Range("L6").Value <> "" Then ' Change L6 to suit
Range("L6").Value = Range("L6").Value + 1
End If
End With
End Sub

Thanks

 

[Dave Peterson]

Maybe something like:

Sub NewMacro1()
'reset L6 to 0
Sheet2.range("L6").value = 0

'Add 1 to I38
with Sheet1
if isnumeric(.range("I38").value) then
.range("I38").value = .range("i38") + 1
else
msgbox "I38 on sheet1 isn't a number!"
end if
end with
End Sub

I don't see why you'd want to increment L6 if you're going to set it to 0 when
you're done. I'd just set it to 0.

 

[LiAD]

Thanks a lot for your help, much appreciated. Seems to be working well
except for the returning to zero part. I added the part to +1 to L6 again as
that was not included, maybe I've changed something in doing that?

The reason is that whenever some-one enters a number of items they dont need
to see what the last guy done, if so they have to add what they've entered to
the previous - allows confusion.

Is it possible to ask excel to save the file, say 20 seconds after it has
been updated? If it updates every time someone pushes the macro it will take
a while to enter.

The other item I had hoped to be able to get was a register to when each
macro is used - macro name, date and time.

Is this possible as well?

The macro I have at the moment is now:

Sub NewMacro1()
'reset L6 to 0
Sheet2.Range("L6").Value = 0

' Changer to suit
With Sheet2
If Range("L6").Value <> "" Then ' Change L6 to suit
Range("L6").Value = Range("L6").Value + 1
End If
End With

'Add 1 to I38
With Sheet1
If IsNumeric(.Range("I38").Value) Then
..Range("I38").Value = .Range("i38") + 1
Else
MsgBox "I38 on sheet1 isn't a number!"
End If
End With
End Sub

 

[Dave Peterson]

I don't understand.

You want to add 1 to the value in L6, but then set it to 0. Why?

Why not just set it to 0 and be done with it?

===
You can use an application.ontime macro to save the workbook every so often.
But I wouldn't bother. I'd just add another button that would save the
workbook.

Sub SaveMeNow()
thisworkbook.save
End sub

========
And you could create a new worksheet (I called it Log) and kept track on that
sheet.

Then each sub would have to have a line added to it:

Option Explicit
Sub NewMacro1()

Call AddToLog("NewMacro1")

'rest of code here!

End Sub
Sub AddToLog(MacName As String)

Dim NextRow As Long

With Worksheets("Log")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(NextRow, "A").Value = MacName
.Cells(NextRow, "B").Value = Now
End With
End Sub

You'll have to change the name passed to the procedure to match each of your
named procedures.

 

[LiAD]

The situation is as follows.

Two groups of people will access this file one as providor of materials the
other as user. The provider will make deliveries several times per day. In
order to check that they have entered the correct data, they need to check
the load to be delivered against what they enter on the computer. If the
screen shows zero then the person hits the button 4 times, looks at his
delivery he can see that he has delivered 4 items so everything is fine. The
next guy that comes will do the same thing. If the total is cumulative then
the second guy needs to add his load from the last total and check that to
see if all is ok, (all beit it super simple). It makes things very quick and
less likely to have mistakes from this guy.

The user will then see a different screen - his interest is the total
stocks, hence why he will see the cumulative from which he will subtract what
he uses. Hence why I need the providor to enter what he deliveres and then
zero and the users screen to hold the cumulative deliveries.

I agree it seems overcomplicated, but there is high tendency for mistakes in
this area, even if its simple. Best not to have the option for mistakes.

Does that help?

 

[LiAD]

Works perfectly.

Thanks a lot

I don't understand.

You want to add 1 to the value in L6, but then set it to 0. Why?

Why not just set it to 0 and be done with it?

===
You can use an application.ontime macro to save the workbook every so often.
But I wouldn't bother. I'd just add another button that would save the
workbook.

Sub SaveMeNow()
thisworkbook.save
End sub

========
And you could create a new worksheet (I called it Log) and kept track on that
sheet.

Then each sub would have to have a line added to it:

Option Explicit
Sub NewMacro1()

Call AddToLog("NewMacro1")

'rest of code here!

End Sub
Sub AddToLog(MacName As String)

Dim NextRow As Long

With Worksheets("Log")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(NextRow, "A").Value = MacName
.Cells(NextRow, "B").Value = Now
End With
End Sub

You'll have to change the name passed to the procedure to match each of your
named procedures.