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.

 

[Instantaneo]

You're absolutely right! I thought about that in the beginning, but as I'm "training myself" to be as efficient in the code as possible, I put that away. But for this case, let's be pragmatic!

Beside, it's even more flexible when it comes to the images names.

Thank you again CJ!

PS - I'm sure I'll be back today with my "browse button problem", because I couldn't manage to fix it yet...

 

[MrIfOnly]

You're quite welcome!

Sometimes the best solution is the simplest one (even if it's not the most elegant).

May I be so bold as to make another pragmatic suggestion? I noticed in your code you used a lot of variable & object names such as:

date_update, date_update_bool, Config.Label_General

Because VBA uses "_" to denote calling an event procedure, and "." to denote a property, you may want to use instead these conventions:

dateUpate, dateUpdateBool, configLabelGen, etc.
It's also common to use all caps for constants such as: Const MYVAL as Long.

These aren't steadfast rules, and most experts will tell you that whatever convention you use, to just be consistent throughout your code. But, this will make your code more readable, IMHO.

Regards,

CJ

 

[Instantaneo]

Hi CJ, I totally agree with your suggestion, I'll try to avoid the "_". In that "." case, it was specifically a label "Label_General" inside the Config userform, so the "." was not inside the name of the variable.

So... I'm back with another doubt!

As I told you, I'm editing some worksheets and exporting it as csv. Since these csv require some "," and ";" in the middle, I searched for days until find the solution you'll see (to guarantee that the common error of "" in the end of strings with "," didn't happen).

But I have another problem... when I copy the sheet to the new file and save it, the file remains opened in the background... even with that "ActiveWorkbook.Close" (or the Workbooks(file).Close that I had before).

This wasn't a problem when I was closing my file with an Application.Quit, but now someone reminded me that if someone opens my file with another excel file already opened it will close it too... then I'm only closing my file, and those txt files still opened remain there.

Please find below the code that might be causing it...

Sub create_csv_KPI()
    Dim file As String
    Dim PathName As String
    Dim ws As Worksheet


    Dim newfiletxt As String
    Dim newfilecsv As String
    
    file = "ficheiro.txt"
    PathName = Me.Box_Folder_Export.Value
    ThisWorkbook.Sheets("folha").Visible = xlSheetVisible
    ThisWorkbook.Sheets("folha").Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=PathName & "\pasta\" & file, _
        FileFormat:=xlTextPrinter, CreateBackup:=False
    ActiveWorkbook.Close
    'Workbooks(file).Close
    ThisWorkbook.Sheets("folha").Visible = xlSheetVeryHidden
    Application.Visible = False
    newfiletxt = PathName & "\pasta\" & "ficheiro.txt"
    newfilecsv = PathName & "\pasta\" & "ficheiro.csv"
    
    If Not Dir(newfilecsv) = "" Then
    Kill newfilecsv
    End If
    
    Name newfiletxt As newfilecsv
    Application.DisplayAlerts = True
End Sub

Thanks again in advance...

 

[MrIfOnly]

A few thoughts:

I think the line:

Workbooks(file).Close

didn't work because the variable 'file' points to your txt file. Try it with the workbook name.

You can also try:

Application.ActiveWindow.Close

Regards,

CJ

 

[Instantaneo]

Sorry for only getting back to you again, CJ!

Thank you again for your help!

Let's go to the next problem: I have a pivot table that has a variable range, but I already managed to update it. However, pivot charts aren't as flexible as I want: I want the pivot table to have always the same columns and only the rows are variable, and if I delete a variable in the pivot chart it will delete it also in the pivot.

So I built a individual pie chart, and I want the results to be like this:


[TABLE="width: 646"]
<tbody>[TR]
[TD][/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]0[/TD]
[TD]0,2[/TD]
[TD]=COUNTIF($T$6:$T$5024;"<"&Z22)-SUM(AA$21:AA21)[/TD]
[TD]2464[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]0,2[/TD]
[TD]0,4[/TD]
[TD]=COUNTIF($T$6:$T$5024;"<"&Z23)-SUM(AA$21:AA22)[/TD]
[TD]2436[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]0,4[/TD]
[TD]0,6[/TD]
[TD]=COUNTIF($T$6:$T$5024;"<"&Z24)-SUM(AA$21:AA23)[/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]0,6[/TD]
[TD]0,8[/TD]
[TD]=COUNTIF($T$6:$T$5024;"<"&Z25)-SUM(AA$21:AA24)[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]0,8[/TD]
[TD]1,2[/TD]
[TD]=COUNTIF($T$6:$T$5024;"<"&Z26)-SUM(AA$21:AA25)[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

(I know I could do it with COUNTIFS, but I'm afraid that someone without Excel'07 uses it)

If I do this with formulas, you have the results you're seeing. But if I try to use VBA, something is wrong...

(please ignore the fact that only the first part of the formula is still being coded)

    Dim pivot_total As Integer
    Dim range_pivot As range
    Dim lim_max As Double
    Dim ws As Worksheet
    Dim first_value As Integer


    Set ws = Worksheets("Sheet")
    ws.Select
    pivot_total = Application.WorksheetFunction.Match("Grand Total", range("K:K"), 0)
    lim_max = range("z22").Value
    Set range_pivot = range("T6:T" & pivot_total - 1)
    first_value = WorksheetFunction.CountIf(range_pivot, "<" & lim_max)
    range("ab22").Value = first_value 
    MsgBox first_value

This is always returning zero...

 

[MrIfOnly]

Hello again:

I'm not nearly as proficient with pivot tables and pivot charts as others on this board, so I think you would be better off posting this as a new thread to try to get the attention of some of the pros.

I will make one comment on your code, though: I noticed that in this line:

first_value = WorksheetFunction.CountIf(range_pivot, "<" & lim_max)

you use a comma after range_pivot, whereas in the worksheet formula you use a semicolon. I believe this is a regional thing but I'm not altogether certain how it applies in VBA. Maybe you could change it to see what happens.

Regards,

CJ

 

[Instantaneo]

Hi again CJ.

Regarding your suggestion, I'm not familiarized with the forum rules so I'm had doubts if each doubt should be addressed in different topics or I should continue my topic with new doubts. I also didn't find the option to change the topic name so it would fit better these new doubts.

Also, I managed to understand that it's not the "pivot related" data that is causing the problem, since I tested with another columns of data... so I believe the problem is in the COUNTIF formula. But I'm doing exactly as I saw in other topics and foruns about this topic.

Regarding your "," comment, I'm clearly not the right guy to speak about that, I only can say that although I have the ";" as separator in the Excel, all the VBA formulas are working with "," as separator. Also, while writing the formula " WorksheetFunction.CountIf(", is asks for "," instead of ";".

Thank you for your help... let's see if anyone else can help me in this topic.