VBA - Updating label with information from another UserForm (and other small doubts from building a GUI in VBA)

[Instantaneo]

Hi there,

I'm barely new to Excel VBA, and all I know about it I learned in a training couse I took a few months ago and from dozens of Google searches, so I might be doing some really-basic mistakes... sorry for that!

I'm building a GUI for a tool that uses SQL and txt files to run (built by another company), so I was challenged to use that knowledge from the VBA training course to try to develop the GUI in VBA... ok, maybe not the best language to create a GUI, but that's all I have (and all I can try to use).

So, I have a UserForm1 with 3 buttons: "General configuration", "Parameters configuration" and "Paths configuration". Each one of the buttons unload UserForm1 and open UserForm2/3/4. In UserForm2 ("General configuration"), I have some textboxes to be filled in and then a button saying "Update" to click.

I would want that when I click in "Update" button the moment is "saved" and in UserForm1 (when showed again) the date of "last updated moment" would be presented in a label.

What I did was adding in the UserForm2 code:

    Public date_update As Date
    Public date_update_bool As Boolean

    (...)

    date_update = Now()
    date_update_bool = True

... and then in UserForm1:

    If date_update_bool = True Then
        Config.Label_General.Caption = date_update
    Else
        Config.Label_General.Caption = "Not updated yet!"
    End If

... but it is not working. What am I doing wrong?

Also, another minor doubt: is there any way of diagnose our code? I mean, since I'm building a GUI of course I don't want the code to break somewhere. So I'm trying to run through every possible mistakes (e.g., a textbox that needs an integer), creating warnings and stuff like that to avoid problems. But is there a way of running "all possible" errors in our code? For instance, I managed to discover that when I have a "browse" button and I click Cancel in the folder search, the code crashes. But if I haven't clicked Cancel, I wouldn't know that and the user would face the issue. Hope you understand my doubt.

Thank you so much for your attention. Please forgive me for wasting your time if this is such a stupid mistake.

 

[MrIfOnly]

Hi:

What's happening is that your variables are being reset when you leave Userform2 and go back to Userform1. What you could do is put this code into a standard module:

Public date_update As Date
Public date_update_bool As Boolean


Sub update()
    
    date_update = Now()
    date_update_bool = True
End Sub

Note that the variable declarations are above any procedure which will make them public to your entire project. Now, simply call the sub "update" from Userform2 like this:

Private Sub CommandButton1_Click()
    update
End Sub

Now, when you have Userform1.show the variables will have the stored values from the sub "update" and you can use them in your label or otherwise do with them what you wish.

Regards,

CJ

 

[Instantaneo]

Thank you so much, it worked fine!

 

[Instantaneo]

My new doubt...

I'm trying to have a spin button to change the images that are shown. Let's say I have Image1 and when I click the "down button" it should change to Image2.

I'm trying to use something like this, that it isn't working (previously it has another sub when the userform activates that sets image_number to 1):

Private Sub SpinButton1_SpinDown()

Dim image_number As Integer
Dim imageshow As String



    imageshow.Visible = True
    image_number = image_number + 1
    If image_number > 10 Or image_number < 1 Then
        Cancel = True
    Else
        imageshow = "Image" & image_number
        imageshow.Visible = True
    End If
            
End Sub

I understand that it shouldn't be working because imageshow is a string and not an "image". But I don't know how to make the image name variable according to the number defined by the spin button...

Thanks in advance!

 

[MrIfOnly]

I have found that the Tag property of images is a bit more flexible to deal with than the Name property. So, go thru each of your images and number their tag property 1 thru 10 (or however many you have). Next, make sure that the properties of your scrollbar are set as follows: Max = 10 (or however many images you have that you want to scroll thru), Min = 1, Value = 1.

Then, paste this in your Userform module:

Private Sub SpinButton1_Change()
    Dim imageCtl As Image
    
    On Error Resume Next
   
    For Each imageCtl In Controls
        If imageCtl.Tag = SpinButton1.Value Then
            imageCtl.Visible = True
        Else
            imageCtl.Visible = False
        End If
    Next imageCtl
    
End Sub

Regards,

CJ

 

[Instantaneo]

Thank you MrIfOnly, but I'm not getting any results with only 2 images...

Sub UserForm_Activate()

Dim image_number As Integer
Image1.Visible = True
Image2.Visible = False


End Sub


Private Sub SpinButton_Tutorial_Change()
    Dim imageCtl As Image
    On Error Resume Next
   
    For Each imageCtl In Controls
        If imageCtl.Tag = SpinButton_Tutorial.Value Then
            imageCtl.Visible = True
        Else
            imageCtl.Visible = False
        End If
    Next imageCtl
    
End Sub

 

[MrIfOnly]

And you have the Tag properties of the images set to 1 & 2 (no quotes, or other marks)?

CJ

 

[Instantaneo]

And you have the Tag properties of the images set to 1 & 2 (no quotes, or other marks)?

CJ

Yes, I do indeed!

 

[MrIfOnly]

Hmmm...strange, it works fine for me whether 10 or 2 images. I had a couple of other ideas about how you can do this, but I'm going to have to work on them a bit. I'll try to have something tomorrow if no-one else here comes up with something in the meantime.

Regards,

CJ

 

[MrIfOnly]

Hey, since you only have 2 images how about using this:

Private Sub SpinButton1_Change()
    If Image1.Visible = True Then
        Image1.Visible = False
        Image2.Visible = True
    Else
        Image1.Visible = True
        Image2.Visible = False
    End If
End Sub

Regards,

CJ