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

GlennW81

New Member
Joined
Aug 19, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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:

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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi there

Not sure if it will work but if you change:

VBA Code:
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:

VBA Code:
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?
 
Upvote 0
Your unload won't actually happen until that code finishes. What is the point of unloading it just to reload it again immediately anyway?
 
Upvote 0
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:

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top