reloading user form issue, code is skipping the reload user form statement or producing error

[GlennW81]

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:

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:

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

 

[Jimmypop]

Hi there

Not sure if it will work but if you change:

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

With:

Start_data_conv.Show (vbModeless) 'vbmodeless allows user to interact with the sheets before pressing userform button

I have tested with Call Reload_userform as well and creating a sub for this which does reload mine... Maybe some other code is causing error?

 

[RoryA]

Your unload won't actually happen until that code finishes. What is the point of unloading it just to reload it again immediately anyway?

 

[GlennW81]

Hi @Jimmypop , as explained in my initial post, I have tried using the Start_data_conv.Show (vbModeless) line instead of Call Reload_userform. When I use the Start_data_conv.Show (vbModeless) line I get the error message "Object Doesn't Support this Property or Method".
It is a fair point @RoryA , I have tried removing the unload, reload functions and having an If statement to only unload if all worksheets contain data. I have tried to do this by changing the boolean to false then testing for the boolean in the if statement, the intend is that the user form will remain open (if no data) or it will unload and call the main code. However I am still getting the same issue - the workbook just closes without doing anything.

The amended code is:

Private Sub Start_data_conv_button_Click()

Dim ws As Worksheet
Start_data_conv_button = True
'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
        Start_data_conv_button = False
    End If
Next ws

If Start_data_conv_button = True Then 'if all worksheets contain data then boolean should be True
    Unload Start_data_conv
    Call org_columns_rows
    Else 'if no data in worksheet then the above For loop should change the boolean to false then user form will remain open and enter the DoWhile loop.
        Do While Start_data_conv_button = False
            DoEvents
        Loop
End If
End Sub

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: reloading userform issue, and code skipping the reload userform statement
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[GlennW81]

Hi,
It has been three weeks now and I have not been able to find a solution to what seems like a simple issue. Could really use some help on this one. Cheers

 

[RoryA]

It would probably help if you could post a sample file somewhere, but in the meantime, why are you using the button name as a Boolean variable?

 

[GlennW81]

Thanks @RoryA , I think that could have been my issue having the button name as a Boolean variable. I have renamed the variable so that it is not the same as the button. The variable was also not declared in the button click or This workbook (where the userform was called from). I had had the variable publicly in the Main code, and as the main code happens after the userform the boolean variable may not have been used set.
Since making those correction - renaming the boolean variable and declaring it in the userform or ThisWorkbook, the code now works.