use excel while user form open

Mikal

Board Regular
Joined
Aug 31, 2015
Messages
73
I ma trying to set my project so that when my userform is open, I can still select and edit cells. I have tried setting the model to false and using vbmodeless but when I do that the form just blinks and goes away. Any ideas?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Only use the Showmodal - false property in the userform properties window.

6da2afad9bfc40756aad6f228491d0c1.jpg


Do you have any other code running on the sheet?
 
Last edited:
Upvote 0
You have to adjust some code.

When a form loads modally, the code stops on userform.Show. When the form loads nonmodally, the code keeps running. So you have to stop running code, and commence when the user dismisses the form or takes some other action. You just need to figure out where to change things.
 
Upvote 0
Thanks everyone for the help. There is a lot of code that runs when ever I click this button. However I think it may be in this code as it makes it run a loop. Is it something here or should I be looking at the rest of the code? I am new at this and I did not write the code, just trying to modify it to meet changing needs. Do you to see where to adjust it in this code?
Code:
Private Sub Recalculate()
'Button added by Michael Rutz on 6/1/16 to recalculate values that are manually edited in the spreadsheet

Dim SearchPN As String
'Runs rows 7 through 47 through for loop
For I = 7 To 47
    'Activates the start here tab then searches the cells in column 1 row i
    Sheets("StartHereSubCom").Activate
    
    'If cell value is not equal to nothing then run code
    If Cells(I, 1) <> "" Then
    'Sets value of SearchPN to column 1 row i value
    SearchPN = CStr(Cells(I, 1).Value)
    'Make SearchSheet Active
    Sheets("Hidden2").Activate
    'Insert SearchPN (copied from original)
    Range("A4").Value = SearchPN
    'Dim SearchCheck1 As String
    'Dim SearchCheck2 As String
    'SearchCheck1 = CStr(Range("A4").Value)
    'SearchCheck2 = CStr(Range("A7").Value)
    'If SearchCheck1 = SearchCheck2 Then
        'If In_SearchPN.Value
        If Range("A4").Value = Range("A7").Value Then
    
        EditSubCom.Show
   
        Else
         MsgBox ("Part Number Not Found.") & vbNewLine & ("Please try your search again.")
    
            SearchPN = ""
        End If
    
    
    End If


Next
Unload EditSubCom
Sheets("StartHereSubCom").Activate
End Sub
 
Upvote 0
You need to look for the data in cell A7 of the "Hidden2" sheet within the range A4:A47 of the "StartHereSubCom" sheet, if it exists then open the userform?
 
Upvote 0
If the above is correct, then try this:

Code:
Private Sub Recalculate()
  Dim f As Range
  Set f = Sheets("StartHereSubCom").Range("A7:A47").Find(Sheets("Hidden2").Range("A7").Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    EditSubCom.Show
  Else
    MsgBox ("Part Number Not Found.") & vbNewLine & ("Please try your search again.")
  End If
End Sub

Note: only use the Showmodal - False property
 
Upvote 0
When I try that I get the message "partnumbr not found" It is not loading the part number from the "StartHereSubcom" page to the a4 location on the "Hidden2" page
 
Upvote 0
Try
In cell A7 of the sheet "Hidden2" put a value, that value must be in the range A7:A47 of the sheet "StartHereSubCom"
Do the above and try the code again.
Forget for a moment the data in cell A4, that is not necessary.
 
Last edited:
Upvote 0
Hi Dante,

When Io that the form will stay open, but now it does not go through the loop. What normally happens is when I open the form it picks up the first part number in A7 of the StartHereSubCom page and puts it on the hidden 2 page. I then do my mods, click the modify button, it goes through the loop and then does the next part number in the column until I goes through all the part numbers. Now it closes after the first part number, and when I click on it again it repeats the first number again.
 
Upvote 0
The cycle does not stop when you open form, that is, the form has control until the cycle ends.


The logic must be done backwards. Open the form and from the form you run the cycle.


Or forget the code for a moment and explain what you want to do.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top