Lose focus on form and set focus to worksheet, but continue to have form running

[tanj92]

I've been scratching my head over this problem for a few hours and searched for possible solutions, but no luck yet.

What I have is a splash screen form that pops up with a few command buttons and togglebuttons.

When I click on the buttons, they work fine and execute properly onto the worksheet. However, the worksheet is not clickable because the form still has focus.. I have to close the form (which I don't want to) and then focus is back to the worksheet.

What I want to do is the following:

1. Have the form running at all times
2. Even when the form is running, I can click on cells on the worksheet, update data, etc then I can go back to the form and click on something else else.

My form basically has two buttons, one is a toggle button to automatically sort and subtotal and one command button to create a duplicate worksheet.

Thanks in advance!

 

[VoG]

Try like this

UserForm1.Show vbModeless

 

[tanj92]

Try like this

UserForm1.Show vbModeless

that works but when i click on the button on the form it goes away afterword.

when i open the spreadsheet, i can click on both the form and a cell, but when i click on a button, the form goes away.

anyway to make it running all the time?

thanks!

 

[VoG]

I don't think that should happen. What is the code for your button?

 

[tanj92]

I don't think that should happen. What is the code for your button?

The button i clicked, calls this sub

Sub createCountySheet()

        'creates new sheet which is a copy of primary
        If wsExists("County") = False Then
        
            Sheets("Primary").Select
            Sheets("Primary").Copy Before:=Sheets(1)
            Sheets("Primary (2)").Select
            Sheets("Primary (2)").Name = "County"
            Call deleteButtons
            Application.Run "Final.xls!autoSortCountyandSubtotal"
            ActiveSheet.Outline.ShowLevels RowLevels:=2
            Columns("A:A").Select
            Selection.Replace What:=" Total", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            Range("A1").Select
            Sheets("Primary").Select
            Range("A1").Select
            
        Else
        
            'confirms if user wants to delete the duplicate county sheet
            Call deleteCounty
        
        End If

and wsExists is a function

Function wsExists(wksName As String) As Boolean

    On Error Resume Next
    wsExists = CBool(Len(Worksheets(wksName).Name) > 0)
    On Error GoTo 0

End Function

 

[VoG]

I cannot see how that is dismissing the UF Naybe add a line

userform1.visible=true

 

[tanj92]

I cannot see how that is dismissing the UF Naybe add a line

userform1.visible=true

i put it after the call sub and it gives me an error

Compile error:
Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic

 

[tanj92]

What's funny is i tried the two toggle buttons on the UF and they work! :D

just the command buttons for some reason

the code for the toggle button

If ToggleButton1.Value = True Then

    Columns("A:A").Select
    Range("A1:H418").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range _
        ("D2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
    
    Columns("A:H").Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        Range("A1").Select

Else

    Columns("A:H").Select
    Selection.RemoveSubtotal
    Range("A1").Select
    
End If

it basically sorts the data in order then subtotals it. when undepressed, it goes back to normal and removes the subtotal.

 

[VoG]

Look here http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/

 

[tanj92]

actually nvm...it works now.. i had to delete this one sub it was calling.

Thanks for the help!

Works great now! :D