Allow viewing other sheets while Userform is open within a loop-CodePudding

I made a workbook that has a userform thar is used to fill information in a new row, the information in the textboxes should be prefilled by using the information on the row below. This has to be repeated as many times as an input box value. So far so good, but now I also need the users to be able to view other sheets in the same workbook where the required information is stored while the userform is open.

if I show the userform modeless I can view other sheets but then the code just keeps going and the second time the userform should pop up it doesn't.

I found a solution to that: using DoEvent. but now the information is not (pre)filled correctly

Private Sub CommandButton2_Click()

Dim myValue As String

myValue = InputBox("How many do you have?")

 If StrPtr(myValue) = 0 Then Exit Sub
 

For i = 1 To myValue
Range("A4").EntireRow.Insert

UserForm1.Show vbModeless

    Do While UserForm1.Visible
         DoEvents
      Loop


Next



End Sub

What happens now is that the information from a row below is used regardless of any changes made by the user.

Does anyone have a solution?

Edit: I don't think it is immediately required to understand my question but it might help a bit.. The rest of the code from the userform is as follows

Private Sub CommandButton1_Click()

Unload UserForm1


End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        
        Range("A4").EntireRow.delete
        End
    
    End If
End Sub



Private Sub TextBox1_Change()


Dim myValue As Variant
myValue = TextBox1
Range("A4").Value = myValue

End Sub

Private Sub UserForm_Initialize()

Me.TextBox1.Value = Format(Range("A2"), "dd/mm/yyyy")
Me.TextBox2.Value = Range("B5").Value
Me.TextBox3.Value = Range("C5").Value
Me.TextBox4.Value = Range("D5").Value
Me.TextBox5.Value = Range("E5").Value
Me.TextBox6.Value = Range("F5").Value
Me.TextBox7.Value = Range("G5").Value
Me.TextBox8.Value = Range("H5").Value
Me.TextBox9.Value = Range("J5").Value
Me.TextBox10.Value = Range("K5").Value



End Sub

Private Sub TextBox10_Change()

Dim myValue As Variant
myValue = TextBox10
Range("K4").Value = myValue
End Sub

Private Sub TextBox11_Change()

End Sub

Private Sub TextBox2_Change()

Dim myValue As Variant
myValue = TextBox2
Range("B4").Value = myValue
End Sub

Private Sub TextBox3_Change()

Dim myValue As Variant
myValue = TextBox3
Range("C4").Value = myValue
End Sub

Private Sub TextBox4_Change()

Dim myValue As Variant
myValue = TextBox4
Range("D4").Value = myValue
End Sub

Private Sub TextBox5_Change()

Dim myValue As Variant
myValue = TextBox5
Range("E4").Value = myValue
End Sub

Private Sub TextBox6_Change()

Dim myValue As Variant
myValue = TextBox6
Range("F4").Value = myValue
End Sub

Private Sub TextBox7_Change()

Dim myValue As Variant
myValue = TextBox7
Range("G4").Value = myValue
End Sub

Private Sub TextBox8_Change()

Dim myValue As Variant
myValue = TextBox8
Range("H4").Value = myValue
End Sub

Private Sub TextBox9_Change()

Dim myValue As Variant
myValue = TextBox9
Range("J4").Value = myValue
End Sub
~~

CodePudding user response：

I found a way.. I now changed the sub names of the textbox#_change subs and call them all on "userform unload".

Private Sub CommandButton1_Click() ' this is the command button on the userform

Call TX1


Unload UserForm1


End Sub

CodePudding user response：

I figured that it indeed had to do with the fact that your initial code did not retrigger the TextBox#_Change subs as intended. I did it a little differently, and triggered them in CommandButton2_Click. This way, you don't need to reload really. But whatever works; just sharing for comparison. So, I am assuming a UserForm like this:

We will move row 4 down on Confirm Input. On Cancel, we'll clear it and exit. And on Confirm Input, the user will (continuously) be asked whether he wants to submit another entry. If not, we'll clear row 4 and exit as well.

So, I've rewritten these parts:

Private Sub CommandButton1_Click()

Range("A4").EntireRow.ClearContents

Unload UserForm1

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        
        Range("A4").EntireRow.ClearContents
        Range("A4").Resize(1, 11).Interior.Color = vbYellow
        End
    
    End If
End Sub

Private Sub CommandButton2_Click()

Range("A4").Resize(1, 11).Interior.Color = vbWhite
Range("A4").Resize(1, 11).Insert

Range("A4").Resize(1, 11).Interior.Color = vbYellow

For i = 1 To 10
    myValue = Me.Controls("TextBox" & i).Value
    Me.Controls("TextBox" & i).Value = ""
    Me.Controls("TextBox" & i).Value = myValue
Next i

answer = MsgBox("Do you wish to add another row?", vbYesNo)

If answer = vbYes Then

    Else

Range("A4").EntireRow.ClearContents

Unload UserForm1

End If

End Sub

Private Sub TextBox1_Change()

Dim myValue As Variant
myValue = TextBox1

If myValue = "" Then
Range("A4").Value = myValue
Else
Range("A4").Value = CDate(myValue)
End If

End Sub

You might want to get rid of the color (re)setting bits. But it may be good to realize that the practice of inserting rows all the time may have unintended effects for formatting. Suppose, for whatever reason, you want row 6 to have a red background. As is, the code will keep pushing this formatting one row down each time. This may be what you want, of course... Other than that, the "update" for TextBox1_Change makes sure you export an actual Excel Date, not a string.

Final warning (since we're using vbModeless): be aware that (both in your code and mine) there is no reference to the worksheet. Suppose your user goes into another sheet and clicks Confirm Input there, this will trigger Range("A4").Resize(1, 11).Insert inside the wrong sheet! Seems highly advisable to fix this.