Excel Built in Data Form

[laurawr]

Good Morning

I have a spreadsheet full of data which is hidden and various userforms are in place to navigate.

To modify the data in the spreadsheet I have used the built in dataform on excel.
my code -

Private Sub findBtn_Click()
'unhide index
 
Application.ScreenUpdating = False
ThisWorkbook.Sheets("HPIN Index").Visible = True
ThisWorkbook.Activate
ThisWorkbook.Sheets("HPIN Index").Select
 
'show data form & save on close
ActiveSheet.UsedRange.Cells(2, 4).Activate
Application.CommandBars.FindControl(ID:=860).Execute
ThisWorkbook.Save
 
'make index hidden
ThisWorkbook.Sheets("HPIN Index").Visible = False
Application.ScreenUpdating = True
End Sub

It was activesheet.showdataform but the date was show in american format, the code above does exactly the same but changes the date to UK format as in the spreadsheet.

Now my problem is, when i want to look at other documents when the dataform is open, ill open the other document e.g. word, come back to my excel userform and the dataform has disappeared behind all the applications and i have to press alt+tab to find it. Is there anyway to avoid it going behind?

Regards

Laura

 

[Andrew Poulsom]

I can't reproduce that behaviour in Excel 2003. For me the data form is always on top of the active worksheet - it's modal.

 

[laurawr]

Hi Andrew,

Yes i think it is modal but, I have a userform that activates the dataform on top of the userform. if i go to another document it seems the dataform goes underneath the userform.

:/

L

 

[Andrew Poulsom]

Can't you hide the UserForm before showing the data form, then show it again when done?

 

[laurawr]

i could but screen updating is set to false so it looks funny when i try to hide it.

 

[Andrew Poulsom]

i could but screen updating is set to false so it looks funny when i try to hide it.

Can't you toggle it on then off?

 

[laurawr]

sorry to sound dumb but how would i do this?

 

[laurawr]

Oh sorry yes i unserdtand what you mean, i cant toggle the screen updating on at that point as opening the dataform pauses the macro showing the spreadsheet that i have hidden.

 

[Andrew Poulsom]

Like this?

Application.ScreenUpdating = True
Me.Hide
Application.ScreenUpdating = False