Click event to run only once
 

HI all,

How would i have the following code run only if the file was opened a
a template? Prob is, the file is a template and a user clicks to cop
info BUT if opened and clicked again, the data copies again......dat
is only to copy once!!

How do i do this??

Private Sub CommandButton1_Click()
'CopyData1 Range("C10:C37"), "BASE MACHINE"
CopyData1 Range("C45:C115"), "OPTIONS INCLUDED"
End Sub
Private Sub CopyData1(rngC As Range, Target As String)
Application.ScreenUpdating = False
Dim rng As Range, cell As Range
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range
Dim nrow As Long, rw As Long
Dim Sh As Worksheet
nrow = Application.CountIf(rngC, "0")
If nrow = 0 Then Exit Sub
Set Sh = Worksheets("sheet1")
Set rng = Sh.Columns(1).Find(What:=Target, _
After:=Sh.Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If rng Is Nothing Then
MsgBox Target & " Not found"
Exit Sub
End If
Set rng3 = rng
Worksheets("sheet1").Unprotect Password:="jenjen1"
rng.Offset(3, 0).ClearContents
If Application.CountA(rng3) 2 Then
Else
Set rng3 = rng.Offset(3, 0)
End If
rw = rng3.Row
rng3.Resize(nrow * 2, 1).EntireRow.Insert
For Each cell In rngC
If Not IsEmpty(cell) Then
If IsNumeric(cell) Then
If cell 0 Then
Cells(cell.Row, 5).Copy
Sh.Cells(rw, "c").PasteSpecial Paste:=xlPasteValues
Cells(cell.Row, 2).Copy
Sh.Cells(rw, "B").PasteSpecial Paste:=xlPasteValues
rw = rw + 2
End If
End If
End If
Next
Worksheets("sheet1").Protect Password:="jenjen1"
End Su

--
gavme
-----------------------------------------------------------------------
gavmer's Profile: http://www.excelforum.com/member.php...nfo&userid=666
View this thread: http://www.excelforum.com/showthread.php?threadid=26497

Click event to run only once
 

hi,
you need a flag to tell the code that is has already run
once.
add this to the start of your code
if Range("IV1").value = 1 then
exit sub
else
your code
'add this to the end of your code.
range("IV1").value = 1
end if

-----Original Message-----

HI all,

How would i have the following code run only if the file
was opened as
a template? Prob is, the file is a template and a user
clicks to copy
info BUT if opened and clicked again, the data copies
again......data
is only to copy once!!

How do i do this??

Private Sub CommandButton1_Click()
'CopyData1 Range("C10:C37"), "BASE MACHINE"
CopyData1 Range("C45:C115"), "OPTIONS INCLUDED"
End Sub
Private Sub CopyData1(rngC As Range, Target As String)
Application.ScreenUpdating = False
Dim rng As Range, cell As Range
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range
Dim nrow As Long, rw As Long
Dim Sh As Worksheet
nrow = Application.CountIf(rngC, "0")
If nrow = 0 Then Exit Sub
Set Sh = Worksheets("sheet1")
Set rng = Sh.Columns(1).Find(What:=Target, _
After:=Sh.Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If rng Is Nothing Then
MsgBox Target & " Not found"
Exit Sub
End If
Set rng3 = rng
Worksheets("sheet1").Unprotect Password:="jenjen1"
rng.Offset(3, 0).ClearContents
If Application.CountA(rng3) 2 Then
Else
Set rng3 = rng.Offset(3, 0)
End If
rw = rng3.Row
rng3.Resize(nrow * 2, 1).EntireRow.Insert
For Each cell In rngC
If Not IsEmpty(cell) Then
If IsNumeric(cell) Then
If cell 0 Then
Cells(cell.Row, 5).Copy
Sh.Cells(rw, "c").PasteSpecial Paste:=xlPasteValues
Cells(cell.Row, 2).Copy
Sh.Cells(rw, "B").PasteSpecial Paste:=xlPasteValues
rw = rw + 2
End If
End If
End If
Next
Worksheets("sheet1").Protect Password:="jenjen1"
End Sub


--
gavmer
----------------------------------------------------------
--------------
gavmer's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=6662
View this thread:
http://www.excelforum.com/showthread...hreadid=264970

.