Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi macro inputs
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi macro inputs
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi macro inputs
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi macro inputs
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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi macro inputs
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? "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi macro inputs
Works perfectly.
Thanks a lot "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clear inputs macro | Excel Discussion (Misc queries) | |||
4 Macro inputs, 1 out | Excel Programming | |||
summing multi inputs | Excel Programming | |||
Multi Macro | Excel Discussion (Misc queries) | |||
Can we modify the macro to accept multi-inputs? | Excel Programming |