I have a workbook that when it opens automatically opens a user form. When I click on the button I want the code to check if data exists in the spreadsheet. If there is no data a message box will appear asking the user to put data in the spreadsheet. I then want the user form to reload so that the user can click on the button again to attempt to convert the data. However, I can get the msgbox to appear, but when I click on the msgbox Ok button, i get an Object Doesn't Support this Property or Method on the line where I have tried to show the user form. I had thought that it might be because i am using the code in the button click event. But when I do the For Loop (to check if data exists) in the Main code, and I click on the button then nothing happens and the workbook closes. I have attempted using Booleans and DoEvents to try and stop the workbook from closing, but it does not seem to help.
In the Thisworkbook, I have the following code:
in the button click I have:
the sub reload_useform is just the: Start_data_conv.Show (vbModeless) statement. I have tried putting this in the button click event but this is when I get the error message. when I have it in the sub, the workbook just closes without doing anything I have also attempted to put the For each ws at the beginning of the main code. But the button click just skips over it.
I can only ever get the Object Doesn't Support this Property or Method message or on the click event i get no msgbox and workbook closes.
Would appreciate any help you may have to solve the issue
Regards, Glenn
In the Thisworkbook, I have the following code:
VBA Code:
Private Sub Workbook_Open()
Start_data_conv.Show (vbModeless) 'vbmodeless allows user to interact with the sheets before pressing userform button
End Sub
in the button click I have:
VBA Code:
Private Sub Start_data_conv_button_Click()
Dim ws As Worksheet
'I have also attempted to put a boolean here with the boolean set to false in initialise or in main code, but has not helped.
'the following For loop will check to see if the source data has been loaded. If there are empty sheets or _
the data hasn't been loaded from the .dat file (each data row in one column)
For Each ws In ThisWorkbook.Worksheets
If WorksheetFunction.CountA(Cells) = 0 Or IsEmpty(ws.[B1]) = False Then
MsgBox "You have not entered the source code or there is an empty sheet. Please enter source", vbCritical
Unload Me ' i have also attempted using the userform name instead of Me
Call Reload_userform 'if there are empty sheets or not loaded correctly then the userform reloads so that you can try again once data has been loaded correctly
End If
Next ws
Unload Me
Call org_columns_rows ' this is the main code
end sub
the sub reload_useform is just the: Start_data_conv.Show (vbModeless) statement. I have tried putting this in the button click event but this is when I get the error message. when I have it in the sub, the workbook just closes without doing anything I have also attempted to put the For each ws at the beginning of the main code. But the button click just skips over it.
I can only ever get the Object Doesn't Support this Property or Method message or on the click event i get no msgbox and workbook closes.
Would appreciate any help you may have to solve the issue
Regards, Glenn