Aquired Workbook - VBA Question

[xacs]

Hello everybody. I don't post much here, and am hoping somebody doesn't mind helping, because my VBA skills are not excellent.

I acquired a workbook, and have worked through most of the VBA setup. But I am stumped on a few remaining items and why they're set up this way.

Please bear with me, as I'm sure this is basic for most of you.

1) Code under "This Workbook":

I believe that this is to run "Initialize" macro. Not sure what the point of the application commandbars line is for.

Private Sub Workbook_Open() ----- Understand.
Application.CommandBars("Ply").Enabled = True ----- Do not understand.
Run "Initialize" ----- Understand, but uncertain why they separated "Initialize" into a module.
On Error Resume Next ----- Understand.
ActiveWorkbook.RefreshAll ----- Understand.
End Sub

2) Code under "Data" "Module" that is the Sub for the above:

This is the "Initialize" macro. I am not sure how it's connected to the "DataValidation", or what it's doing exactly. When I actually run the macro nothing seems to happen.

Dim X As New DataValidation ----- Understand.
Sub Initialize() ----- Understand.
Set X.qt = ThisWorkbook.Sheets("DATA").QueryTables(1) ----- Do not understand. I believe it's referencing the table in the below class module, but uncertain why it's separated into a class module.
End Sub

3) Code under "DataValidation" "ClassModule":

I understand that this is unprotecting a sheet, updating a table, and updating the Name manager. I'm not certain why it was created a "ClassModule" or if it's directly connected to "Initialize".

Public WithEvents qt As QueryTable ----- Do not understand.

Private Sub qt_BeforeRefresh(Cancel As Boolean) ----- Do not understand.
Worksheets("DATA").Unprotect Password:="-" ----- Understand.
End Sub

Private Sub qt_AfterRefresh(ByVal Success As Boolean) ----- Do not understand.
Worksheets("DATA").Unprotect Password:="-" ----- Understand.
Worksheets("DATA").Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, MatchCase:=False ----- Understand.
Worksheets("DATA").Range("C5:C" & Worksheets("DATA").Range("C" & Rows.Count).End(xlUp).Row).Name = "CATEGORY" ----- Understand.
Worksheets("DATA").Columns("C:J").EntireColumn.AutoFit ----- Understand.
Worksheets("DATA").Protect Password:="-" ----- Understand.
Range("B1").Select ----- Understand.

End Sub

Thank you for your help and time.

 

[GideonW]

Application.CommandBars("Ply").Enabled = True ----- there is a custom menu item called "Ply"
Run "Initialize" ----- it is telling the code to run the subroutine called "Initialize" which you have listed under the Data module [[Sub Initialize() ----- Understand. ]]


Set X.qt = ThisWorkbook.Sheets("DATA").QueryTables(1) -- a QueryTable is built in excel functionality but you are correct that they are also defining it in their own class module.Tbh this seems to be an unnecessary piece of code unless you have not listed the the whole module


Public WithEvents qt As QueryTable -----

Private Sub qt_BeforeRefresh(Cancel As Boolean) ----- Do not understand.
Worksheets("DATA").Unprotect Password:="-" ----- Understand.
End Sub

Private Sub qt_AfterRefresh(ByVal Success As Boolean) ----- Do not understand.


this is best explained in one go; there are a set of macros that usually sit within the Workbook or Worksheet objects that are run based on an event happening; e.g. Code under "This Workbook": go back there and you can see that above the code box you can see (General) in a drop down menu ..if you change that to Workbook, the box to its right now lists all the routines that will run based on certain events that occur in the workbook.

The events are defined by the name of the routine.like Private Sub Workbook_SheetActivate(ByVal Sh As Object) runs when workbook is activated by the user or Private Sub Workbook_BeforeSave will run just before the user saves the worikbook...
These are what are known as event driven......

WithEvents just defines the routines in the Class module in a similar fashion so they will run before the qt is refreshed & after the qt is refreshed (qt_BeforeRefresh & qt_AfterRefresh)
Each one is called based on on its name now . The event will define when the code is run .

btw ,a querytabale is used to pull in external data from somewhere else. There must be one defined in the DATA sheet.Go there and right click.you will see refresh as an option

 

[xacs]

Hello Gideon.

Thank you for your response and information.

1) Code under "This Workbook":

1. Application.CommandBars ----- I have removed this, as I believe that this was left over from the workbook originally trying to remove the right click as an option. Workbook still seems to function well.
2. The rest of the code here is understood. Basically run "Initialize" and refresh the workbook.

2) Code under "Data" Module that is the Sub for the above / 3) Code under "DataValidation" ClassModule:

The code listed above is complete as in the workbook. When I remove or update either of the above sections, I notice that the workbook no longer works as necessary.

If I remove the "Data" module completely, I error "The cell or chart you're trying to change is on a protected sheet..."

The "data" Module and "DataValidation" ClassModule seem to be connected in functionality. It'd be great to understand how / why this is working.

I can tell you the functionality that I need:

1) When the workbook is open, refresh the "Data" worksheet (which is a a google drive table).
2) Update the DataValidation list to the possibly updated table from google.
3) Password protect "Data" worksheet again to prevent users from thinking it's just a normal table.

Seems like it's a lot of back and forth to just get the above to happen. Especially when there is a "Refresh when workbook opens" option for the established connections.