PDA

View Full Version : Solved: Add To Code



BENSON
06-09-2008, 11:51 PM
The code belows clears and prepares a weekly stock sheet ready for the new week. I would like to add to it so when the code is run, it inserts the number one (1) in cell "D1".If the code is run again I would need it to insert the number two (2) in cell "D1" and so on up untill the number (4) is in cell "D1" .When the number four (4) is in "D1" I would like it to revert back to one (1) the next time the code is run. The reason I wish to do this is to link a further code depending on the numbers in cell "D1"

Thanks

Sub Button4_Click()
' Button4_Click Macro
ActiveSheet.Unprotect
If IsEmpty(Range("V5").Value) Then
Dim strAnswer As VbMsgBoxResult
strAnswer = MsgBox("WARNING NO CLOSING STOCK HAS BEEN ENTERED, " & _
"IF YOU CONTINUE THERE WILL BE NO OPENING FOR NEW REPORT ?", _
vbQuestion + vbYesNo, "Decision time!")
ActiveSheet.Protect
If strAnswer = vbYes Then DoCopy
Else
DoCopy
ActiveSheet.Protect
End If
End Sub

Sub DoCopy()
ActiveSheet.Unprotect
Range("V5:V240").Copy Range("C5")
With Range("D5:Q240,V5:W240")
.ClearContents
.ClearComments
End With
Application.CutCopyMode = False
ActiveSheet.Protect
End Sub

Simon Lloyd
06-10-2008, 12:30 AM
This should do it!


Sub Button4_Click()
' Button4_Click Macro
Dim i As Integer
i = 1
ActiveSheet.Unprotect
If Range("D1").Value = 4 Then
Range("D1").Value = 1
Else
Range("D1").Value = Range("D1").Value + i
End If
If IsEmpty(Range("V5").Value) Then
Dim strAnswer As VbMsgBoxResult
strAnswer = MsgBox("WARNING NO CLOSING STOCK HAS BEEN ENTERED, " & _
"IF YOU CONTINUE THERE WILL BE NO OPENING FOR NEW REPORT ?", _
vbQuestion + vbYesNo, "Decision time!")
ActiveSheet.Protect
If strAnswer = vbYes Then DoCopy
Else
DoCopy
ActiveSheet.Protect
End If