Make userform reusable and keep on top

Question

Following is the background: I have a “Home” sheet which has a command button which shows a userform when clicked. The userform takes coordinate inputs on text boxes and it creates rectangles of a new sheet “Rectangles”, once OK command button on userform is clicked. Now, I want the “Rectangles” sheet to be seen when the OK command button on userform is clicked. Also, I want to keep the userform on top, incase user sees that a particular rectangle is not in the desired place and wants to update the coordinates.

I have the following code on the main sub

With rect_box
   .StartUpPosition = 0
   'Position Userform to the centre of screen
   .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
   .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
   .Width = 240
   .Height = 460
   .Show
End With

In the userform rect_box code ok command button, I have the following code

x1 = x1box1.Value 'x1box1, x2box2... are all textbox names
y1 = y1box2.Value
x2 = x2box3.Value
y2 = y2box4.Value
Unload me

I then use the x1, y1, x2, y2 in another sub, to create rectangles on the “Rectangles” sheet. Towards the end of the sub, I put the following to show the sheet.

ThisWorkbook.Sheets("Rectangles").Visible = True
ThisWorkbook.Sheets("Rectangles").Activate 

Now, if I don’t use “Unload me”, then once I click OKcommand button, I cannot move ahead with the code.

If I make the userform nonmodal (.show False), then the “Rectangles” sheet is shown and I have the userform on top of it as I intend. However, I am unable to reuse it to change values and reuse the command to reposition the rectangles.

'Option 1:
    With rect_box
        .StartUpPosition = 0
        'Position Userform to the centre of screen
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Width = 240
        .Height = 460
        .Show.False
    End With

The whole macro runs, without giving user a chance to input values in the text box in the first place. The sheet has no rectangles and userform is overlayed. Now, when input is added and ok is clicked after, nothing happens, but the userform closes.

    'Option 2:
        With rect_box
            .StartUpPosition = 0
            'Position Userform to the centre of screen
            .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
            .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
            .Width = 240
            .Height = 460
            .Show
        End With
rect_box.show False

User can input values once. The sheet shows up with rectangles and has the userform overlayed. But then the user cannot reuse the form. Pressing ok does nothing but close the userform.

How do I go around it?

Thank you for the support.

Answer 1

x1 = x1box1.Value 'x1box1, x2box2... are all textbox names
y1 = y1box2.Value
x2 = x2box3.Value
y2 = y2box4.Value
'Unload me

You can potentially remove the unload me section. This will let you keep the values in play. You can also use the UserForm.Hide to keep the userform out of view.