0

I'd like to show a UserForm, but allow the user to look through worksbooks and worksheets. I know I can hide the UF, but I'd prefer to leave up the UF. Is this possible? I know there's vbModeless, but the code continues to execute, which is not what I want.

My code so far looks somewhat like this:

Sub Test()
Dim Counter As Long
Dim wb As Workbook

Application.ScreenUpdating = False

 '-------main code

If Counter <> 0 Then
    frmTest.Show 
End If
Unload Me
For Each wb In Application.Workbooks
    If wb.Name Like "Report*" Then wb.Close SaveChanges:=True
Next wb

Application.ScreenUpdating = True 

End Sub

I could change the if-statement to

If ErrorCount > 0 Then
       frmTest.Show vbModeless
    Else
        Call SaveWorkbooks
    End If
Application.ScreenUpdating = True
End Sub

And create another sub:

Sub SaveWorkbooks()
Dim wb As Workbook
    Unload frmTest
    For Each wb In Application.Workbooks
        If wb.Name Like "Report*" Then wb.Close SaveChanges:=True
    Next wb

End Sub

I guess that works, but

  1. Is it good practice splitting up your code like that?
  2. What if this occurred in the middle of my main sub? That would make 1) even worse.

Is there a more elegant solution

NoNameNo123
  • 625
  • 1
  • 8
  • 17
  • I think you need to elaborate more: are the workbooks the user can browse A) workbooks user opens, or B) workbooks the userform code opens? – Mistella May 01 '19 at 20:45
  • 1
    I'd re-post [this answer](https://stackoverflow.com/a/55926975/1188513), verbatim. You absolutely want `vbModeless`, and definitely split up your procedures. The less a procedure does, the better. – Mathieu Guindon May 01 '19 at 20:55
  • [Related](https://stackoverflow.com/a/47358692/1188513) – Mathieu Guindon May 01 '19 at 21:04
  • @Mistella The workbooks are already open and have been opened by my code (preceding the userform) – NoNameNo123 May 02 '19 at 13:52
  • @MathieuGuindon Ok, so I have no clue about class module and reading up on it and understanding it might take a bit.Definitely thank you for the links, though. In the meantime, if I'm sticking with my non-class code, I assume the "the less a procedure does, the better" still holds true? So for example for my code, I have three different types of data that I copy - should I put those 3 different data types in 3 different procedures and call them from the main one? What's the reason for this? Is it readability or something else? – NoNameNo123 May 02 '19 at 13:59
  • 1
    A form is little more than a class module with a visual designer. A worksheet is a class. A workbook is a class. A range is a class. You've been using classes all along! As for how/where to split things up, it's all about *abstraction levels*: you want your "main procedure" (the public macro) to read like a high-level executive summary of what's going on, and each procedure goes into deeper details about how each part gets done. For readability, yes - but mostly because a procedure that does one thing, has only 1 reason to fail... but first things first: it needs to work, *then* be pretty! – Mathieu Guindon May 02 '19 at 15:13
  • 1
    The problem you're having with a modeless form, is that a modeless form is *inherently* going to *want* your code to go asychronous and enter an event-driven paradigm: any other hacks to keep it all procedural, is essentially *fighting* the very nature of how a modeless form wants to work. And then the hacks have flaws, which cause bugs, which cause more hacks, which inevitably complicate things well beyond the "complexity" of doing things properly. – Mathieu Guindon May 02 '19 at 15:22
  • 2
    Once you get your code to work as intended (pretty or not), I'd recommend you put it up for peer review on [codereview.se] - you'll get answers with infinitely more info/details than whatever can fit in a 400-character comment :) – Mathieu Guindon May 02 '19 at 15:31
  • Thanks a lot! One question about Code Review: Is it encouraged to link to a .xlsx file that shows the data/data structure? I imagine looking at the code with just a description of the data is somewhat difficult. – NoNameNo123 May 02 '19 at 20:02

0 Answers0