Help with code please

[Greg]

Hi I am wondering how to get this macro to retrigger when the user clicks
yes in the Dim Queri section. I have a message box appear and the user can
choose yes or no, but how do I get it to restart from the beggining again?

Private Sub Image20_Click()

Sheets("SEASON").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("D2").Select
Dim ans
Dim PLAYERS As String
Sheet5.Activate
PLAYERS = InputBox("WHAT IS THE PLAYERS REGISTRATION NUMBER?")
On Error Resume Next


ans = Application.Match(CLng(PLAYERS), Range("A:A"), 0)
If Not IsError(ans) Then
Sheet3.Range("A2").Value = Application.Index(Range("A:A"), ans)
Sheet3.Range("B2").Value = Application.Index(Range("B:B"), ans)
Sheet3.Range("C2").Value = Application.Index(Range("C:C"), ans)

Else
End If
On Error GoTo 0


Sheets("SEASON").Select
ActiveCell.FormulaR1C1 = "0"
Range("E2").Select
ActiveCell.FormulaR1C1 = "0"
Range("F2").Select
ActiveCell.FormulaR1C1 = "0"
Range("G2").Select
ActiveCell.FormulaR1C1 = "0"
Range("H2").Select
ActiveCell.FormulaR1C1 = "0"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>0,SUM(RC[-1]/RC[-2]),0)"
Range("F2").Select
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Range("L2").Select
ActiveSheet.Paste
Range("O2").Select
ActiveSheet.Paste
Range("J2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("K2").Select
ActiveCell.FormulaR1C1 = "0"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=RC[-9]+RC[-6]+RC[-3]"
Range("M2").Select
Selection.Copy
Range("N2").Select
ActiveSheet.Paste
Range("P2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "0"
Range("R2").Select
ActiveCell.FormulaR1C1 = "0"
Range("S2").Select
ActiveCell.FormulaR1C1 = "0"
Range("T2").Select
ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-16]/4)>6,""Q"",""NQ"")"
Range("U2").Select
ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-14]/8)>6,""Q"",""NQ"")"
Range("V2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-19]=""M"",RC[-6],0)"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-20]=""F"",RC[-7],0)"
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select

' HERE IS THE PART I AM UNSURE OF?

Dim QUERI
QUERI = MsgBox("IS THERE ANY OTHER PLAYER'S TO ADD?", vbYesNo)
If QUERI = vbYes Then
End If


If QUERI = vbNo Then
Unload Me
MAIN.Show
End If

Thanks

Greg

 

[JMB]

You could use a Do/Loop

Do
..procedures you want repeated
Loop Until Queri = vbno

 

[Greg]

Thanks for that

Greg

You could use a Do/Loop

Do
..procedures you want repeated
Loop Until Queri = vbno

Hi I am wondering how to get this macro to retrigger when the user clicks
yes in the Dim Queri section. I have a message box appear and the user
can
choose yes or no, but how do I get it to restart from the beggining
again?

Private Sub Image20_Click()

Sheets("SEASON").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("D2").Select
Dim ans
Dim PLAYERS As String
Sheet5.Activate
PLAYERS = InputBox("WHAT IS THE PLAYERS REGISTRATION NUMBER?")
On Error Resume Next


ans = Application.Match(CLng(PLAYERS), Range("A:A"), 0)
If Not IsError(ans) Then
Sheet3.Range("A2").Value = Application.Index(Range("A:A"), ans)
Sheet3.Range("B2").Value = Application.Index(Range("B:B"), ans)
Sheet3.Range("C2").Value = Application.Index(Range("C:C"), ans)

Else
End If
On Error GoTo 0


Sheets("SEASON").Select
ActiveCell.FormulaR1C1 = "0"
Range("E2").Select
ActiveCell.FormulaR1C1 = "0"
Range("F2").Select
ActiveCell.FormulaR1C1 = "0"
Range("G2").Select
ActiveCell.FormulaR1C1 = "0"
Range("H2").Select
ActiveCell.FormulaR1C1 = "0"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>0,SUM(RC[-1]/RC[-2]),0)"
Range("F2").Select
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Range("L2").Select
ActiveSheet.Paste
Range("O2").Select
ActiveSheet.Paste
Range("J2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("K2").Select
ActiveCell.FormulaR1C1 = "0"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=RC[-9]+RC[-6]+RC[-3]"
Range("M2").Select
Selection.Copy
Range("N2").Select
ActiveSheet.Paste
Range("P2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "0"
Range("R2").Select
ActiveCell.FormulaR1C1 = "0"
Range("S2").Select
ActiveCell.FormulaR1C1 = "0"
Range("T2").Select
ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-16]/4)>6,""Q"",""NQ"")"
Range("U2").Select
ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-14]/8)>6,""Q"",""NQ"")"
Range("V2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-19]=""M"",RC[-6],0)"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-20]=""F"",RC[-7],0)"
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select

' HERE IS THE PART I AM UNSURE OF?

Dim QUERI
QUERI = MsgBox("IS THERE ANY OTHER PLAYER'S TO ADD?", vbYesNo)
If QUERI = vbYes Then
End If


If QUERI = vbNo Then
Unload Me
MAIN.Show
End If

Thanks

Greg

 

[kartune85]

Maybe you could try putting your code in a function and calling that
function when the image is clicked and then you should also be able to
call the function later on when the user clicks "yes".

E.g.:

Sub AddPlayer()
Sheets("SEASON").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("D2").Select...

etc...
end Sub


Then call it from within:
Private Sub Image20_Click()
Call PlayerAdd
End Sub

Just a thought, I don't know how you've set it up.

Kartune85

 

[Greg]

I sort of did that but I used the activation function of a userform and had
it turn it self off and on to activate the proceddure again.

Thanks for the advice

Greg