Using VBA userform to select ranges on multiple sheets - sheet changes back to original activesheet

0
votes

I have a userform which has multiple RefEdit controls. I need the user to select ranges from multiple sheets and the userform has to be complete before the rest of the code can run.

Issue: The activesheet is "Sheet1" when the userform is initiated. Each time I select a range on "Sheet2" and click into the next RefEdit the visible Excel sheet returns to "Sheet1". I'd like the sheet to remain on "Sheet2", since clicking between the sheets significantly increases the time it takes to select the data.

Because I need the userform to be completed before continuing with my code, using "vbModeless" doesn't appear to work.

I've tried to step through the userform events which appeared to be relevant but none were activated when I entered the RefEdit, selected the data, or left the RefEdit.

Thanks in advance for any help!

Edit: Using some input from the responses and doing some more research I think I've figured out the problem and a work around.

RefEdit events such as Change or Exit (I tried all of them I think) don't appear to trigger when a change occurs in the control. So I couldn't write code to manipulate the activesheet when I changed the control. A workaround found here: http://peltiertech.com/refedit-control-alternative/ uses a textbox and inputbox to simulate a RefEdit control and will actually trigger when changes are made! Code is below. To add other "RefEdit" controls you should repeat the code in the Userform_Initialize event for each control, then add another TextBox1_DropButtonClick and update TextBox1 to the name of the new control. In use when the control updates the workbook jumps to the previous activesheet and then returns the desired activesheet. Not as smooth as I'd like but much better than it was.

Code:

Private Sub CancelButton_Click()
    Unload Me
    End
End Sub
Private Sub OKButton_Click()
    UserForm1.Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    End
End Sub
Private Sub UserForm_Initialize()
    Me.TextBox1.DropButtonStyle = fmDropButtonStyleReduce
    Me.TextBox1.ShowDropButtonWhen = fmShowDropButtonWhenAlways
End Sub
Private Sub TextBox1_DropButtonClick()
    Dim ASheet As String ' Active sheet

    Me.Hide

    'Use input box to allow user to select a range
        On Error Resume Next
            Me.TextBox1.Value = Application.InputBox("Select the range containing your data", _
                    "Select Chart Data", Me.TextBox1.Text, Me.Left + 2, _
                    Me.Top - 86, , , 0)
        On Error GoTo 0

    'Check if there is a sheet name - if the range selected is on the activesheet the output of the inputbox doesn't have a sheet name.
        If InStr(1, Me.TextBox1.Value, "!", vbTextCompare) > 0 Then ' there is a sheet name
            ASheet = Replace(Split(Me.TextBox1.Value, "!")(0), "=", "") ' extract sheet name
        Else ' there is no sheet name
            Me.TextBox1.Value = "=" & ActiveSheet.Name & "!" & Replace(Me.TextBox1.Value, "=", "") ' add active sheet name to inputbox output
            ASheet = ActiveSheet.Name
        End If

    Worksheets(ASheet).Activate ' set the active sheet

    Me.Show

End Sub
2
exceluserformvba

2 Answers

1
votes

Have you tried something as simple as:

Sheets("Sheet2").Select

somewhere in the beginning of your form code ?

Since you haven't posted your code, it's hard to provide a good answer. Hope this helps a little :)

0
votes

This form module worked for me.

Private Sub CommandButton1_Click() 'Cancel Button
Unload Me
End Sub

Private Sub CommandButton2_Click()  'GO Button
Dim newSheet As Worksheet

abc = Split(RefEdit1.Value, "!")
cbn = abc(0)
Unload Me
Set newSheet = Worksheets(abc(0))
newSheet.Activate
End Sub