vba pause while the user paste a list into a sheet

[jscranton]

I have some code that asks the user to paste a list into the active sheet.

I want the code to allow time for the user to do and then either restart or prompt the user to click ok if they are done.

Any suggestions?

 

[pman]

How about using a user form?

 

[jscranton]

What kind of user form allows the user to interact with the active sheet before clicking okay?

 

[pman]

Change ShowModal to false

 

[jscranton]

I created a form and changed Show Modal to false. That allows the user to paste (which is great). But, I want the code to resume when they click okay (Case 1). As it is now, the box stays open until the entire code is complete.

here is my code (and thanks for the help).

Sheets("PasteTeamList").Visible = True
        Sheets("PasteTeamList").Select
 
        On Error Resume Next
 
        Set myForm = formCustomMsgBox2
            myForm.Caption = "Paste Team List"
            myForm.Show
 
 
        Select Case myForm.Tag
 
            Case 1
 
                LR = (Sheets("PasteTeamList").Range("A" & Rows.Count).End(xlUp).Row) - 1
 
                    For i = 1 To LR
 
                            teamList(i) = CStr(Sheets("PasteTeamList").Range("A" & i).Value)
 
                        Next i
 
 
 
 
            End Select
 
    Sheets("PasteTeamList").Visible = False

 

[pman]

Why not create an on click function for the OK button? It could form.Hide then continue the code.

 

[Warship]

would a timer work?

standard module:

Dim RunAt As Date
Const CycSecs As Integer = 1

Sub Button1_Click()
    'adapt here for your code
    Range("A7") = ""
    myForm.Show vbModeless
End Sub

Sub CheckPasteArea()
    If Range("A7") = "" Then
        TimerStart
    Else
        TimerStop
        Unload myForm
    End If
End Sub

Sub TimerStart()
    RunAt = Now + TimeSerial(0, 0, CycSecs)
    Application.OnTime _
        EarliestTime:=RunAt, _
        Procedure:="CheckPasteArea"
End Sub

Sub TimerStop()
    On Error Resume Next
        Application.OnTime _
            EarliestTime:=RunAt, _
            Procedure:="CheckPasteArea", _
            Schedule:=False
    On Error GoTo 0
End Sub

UserForm:

Private Sub UserForm_Activate()
    TimerStart
End Sub

 

[jscranton]

What if I add a list box to my command button?

Is it easy to have the user paste the list into there and once they click okay, transfer it to the active sheet?

 

[pman]

I don't think a list box would work. A multi line text box would be fine.

 

[jscranton]

I bailed on the user form since it doesn't seem necessary with a timer.The goal below is to run the timer for a bit then return to the condition. but, it keeps throwing an endless loop.

Sub createTeamArray()
    ReDim teamList(1 To 500) As String
    Dim i As Long
    Dim myForm As formCustomMsgBox2
 
    totalReviewers = 25
 
 
 
    checkEntryMethod = MsgBox("We need to set up a reviewer list for your " & CStr(totalReviewers) & " person review team. If you want to paste a list, select yes. To enter manually, select no.", vbYesNo)
 
    If checkEntryMethod = vbNo Then
 
        For i = 1 To totalReviewers
 
            teamArray(i) = InputBox("What is the name of your first review in format FirstName M. LastName?")
 
        Next i
 
    Else
 
        Sheets("PasteTeamList").Visible = True
        Sheets("PasteTeamList").Select
 
        On Error Resume Next
 
 
    Call checkPasteArea
 
 
    Sheets("PasteTeamList").Visible = False
 
    End If
 
 
End Sub
Sub checkPasteArea()
 
1:    If Sheets("PasteTeamList").Range("A" & totalReviewers) = "" Then
 
        Application.OnTime Now + TimeValue("00:00:45"), "TeamArray"
        GoTo 1
      Else
 
            LR = (Sheets("PasteTeamList").Range("A" & Rows.Count).End(xlUp).Row) - 1
 
                    For i = 1 To LR
 
                            teamList(i) = CStr(Sheets("PasteTeamList").Range("A" & i).Value)
 
                        Next i
        End If
 
 
End Sub