Hide User Form, Allow Changes To DATA Tab, Return To Hidden Form (Create a return to forum button on tab?)

[realitycheck]

This should be fairly easy...

Sometimes the form is mostly filled with good data... Then the user wants to change something on the DATA tab without cancelling out of the form and losing what was there...

So I created a button on the form called Hide & Change.

Behind which has this running code...

Private Sub CommandButton1_Click()

frmPartLoc.Hide
Sheets("DATA").Select

'something needs to go here to allow manual data input on the data tab and the code needs to create a button that allows you to return to the form upon click right from that data tab.

frmPartLoc.Show

End Sub

Where my comment is is where the break needs to be along with a new button to recall the partially filled form.

Anyone got any ideas on how to make the button appear on the Data Tab which allows for changes to be made and then returns to the form?

 

[steve the fish]

You could change the ShowModal property of the userform to false. Then you can click into the worksheet and edit without losing the form.

 

[realitycheck]

You could change the ShowModal property of the userform to false. Then you can click into the worksheet and edit without losing the form.

Thank you for the quick response Steve. But then how does it go back to ShowModal true? Should it? The reason I'm asking is because it's nice when they are forced to make a conscious effort to leave the form and change data so they can't accidentally do it and if ShowModal was always true this error preventative would not be there.

Also where is this ShowModal=True set?

 

[steve the fish]

They still have to make the conscious decision to click out of the userform. The same as they would to press the button to hide it and press the button to unhide it. You can still have the button to hide on the userform and have another button on the worksheet that unhides it if you prefer that way?

In the vba editor right click your userform in the project explorer window. Press view object. If you cant see the properties window press F4. ShowModal is there.

 

[realitycheck]

They still have to make the conscious decision to click out of the userform. The same as they would to press the button to hide it and press the button to unhide it. You can still have the button to hide on the userform and have another button on the worksheet that unhides it if you prefer that way?

In the vba editor right click your userform in the project explorer window. Press view object. If you cant see the properties window press F4. ShowModal is there.

Thanks a million man, good info. I'll give it a shot and I'll report back here for prosperity.

Thanks again!

 

[realitycheck]

You could change the ShowModal property of the userform to false.

Just brainstorming... can you do this in the module?

Private Sub CommandButton1_Click() 

frmPartLoc.Hide
 Sheets("DATA").Select

  'what's the syntax to set form to ShowModal=False 

frmPartLoc.Show  

End Sub

and if so, how can we return the module to default value True when a otherwise non-existent button on Data tab is pressed.

Thanks in advance!