Thread: Repeat section of a Macro after YES/NO question is asked.

Ok. I have a Macro that runs to basically add a new row to my spreadsheet keeping the formatting and the formulas only. Data gets deleted.
It works perfectly. It has to un-share the workbook to run.

I am basically wanting a question to be asked, after the section of the code that adds new row to see if the user wants another row adding. If they select yes another row gets added. The question will then be asked again until they select no. If they select No, then the macro carries on running and re-shares the workbook.

That way i can have the spreadsheet re-shared at the end rather than expecting the user to do it, as the process currently is at present. The reason i want it to happen this way is if the workbook is re-shared after each row is added, it will take a considerable amount of time.

This is the code i have already. Any ideas?

	
HTML Code: 

	
Sub AddNewRow()
'
' AddNewRow Macro
'
answer = MsgBox("The file needs to be unshared. Has everyone else saved, and come out of this file?", vbYesNo)
If answer = vbNo Then Exit Sub

MsgBox "The workbook will now be unshared. Click OK to continue. This may take some time."

Application.DisplayAlerts = False
If ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.ExclusiveAccess
End If
Application.DisplayAlerts = True

MsgBox "Sucessfully unshared. Click OK to continue."


'
    ActiveSheet.Unprotect
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("3:3").Select
    Selection.Copy
    Rows("2:2").Select
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Rows("2:2").Select
    Application.CutCopyMode = False
    Rows("2:2").Select
    Selection.SpecialCells(xlCellTypeConstants, 3).Select
    Selection.ClearContents
    Range("B2").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:= _
        True
    
    MsgBox "New row added. Please remember to re-share the workbook."
        
        
End Sub

Thanks in advance.

Hi gonzalraf,

This feels like a "GoTo Line Label" type of question. See http://msdn.microsoft.com/en-us/libr...(v=VS.80).aspx for an example or http://www.java2s.com/Code/VBA-Excel...ementworks.htm

The idea is you do some test and then jump back to a Line Label above in your code to do stuff over again.

How about a logic like this?

'pseudo code

Sub theSub()

    Ask beginning questions

    UnShare

    Do
        Add Rows
    Loop While MsgBox("Do it again?", vbYesNo) = vbYes

    Re-share

End Sub

You need to edit your original post and go to advanced and edit the prefix of the thread title to Solved.