use excel while user form open

[Mikal]

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?

 

[DanteAmor]

Only use the Showmodal - false property in the userform properties window.

Do you have any other code running on the sheet?

 

[Jon Peltier]

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.

 

[Mikal]

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?

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

 

[DanteAmor]

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?

 

[DanteAmor]

If the above is correct, then try this:

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

 

[Mikal]

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

 

[DanteAmor]

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.

 

[Mikal]

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.

 

[DanteAmor]

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.