Excel VBA ListView control columns auto-fit (auto-resize)

Briefly, the subroutine code at the end of the page can be used to auto-resize (auto-fit) columns for Microsoft List View control used in user-forms in Excel, PowerPoint, Word, and the rest of MS Office tools. It is a simple, easy to understand, and short method to do this job. It can work with different font types and sizes.

The following pictures shows an actual example for list view control before and after the auto-resize subroutine (data source of table contents from:  http://www.7continents5oceans.com/ )



Column width is previously set to 100

Column width is auto-fit

' A procedure to auto-resize column width of list view control Sub AutoResizeListView(MyListView As Variant)   ' Create a dynamic label and set it invisible Set MyLabel = Me.Controls.Add("Forms.Label.1", "Test Label", True)   With MyLabel     .Font.Size = MyListView.Font.Size     .Font.Name= MyListView.Font.Name     .WordWrap = False     .AutoSize = True     .Visible = False End With   ' Auto-resize the first column MyLabel.Caption = MyListView.ColumnHeaders(1).Text MaxColumnWidth = MyLabel.Width   For i = 1 To MyListView.ListItems.Count MyLabel.Caption = MyListView.ListItems(i).Text If MyLabel.Width > MaxColumnWidth Then MaxColumnWidth = MyLabel.Width End If Next i MyListView.ColumnHeaders(1).Width = MaxColumnWidth + 8   ' Auto-resize the rest of columns For i = 1 To MyListView.ColumnHeaders.Count - 1 MyLabel.Caption = MyListView.ColumnHeaders(i + 1).Text MaxColumnWidth = MyLabel.Width For j = 1 To MyListView.ListItems.Count MyLabel.Caption = MyListView.ListItems(j).SubItems(i) If MyLabel.Width > MaxColumnWidth Then MaxColumnWidth = MyLabel.Width End If Next j     'Next cell row in the current column   MyListView.ColumnHeaders(i + 1).Width = MaxColumnWidth + 8    Next i     'Next column   Me.Controls.Remove MyLabel.Name    'Remove the dynamic label End Sub

This sub can be called using the following method inside the user-form code

Call AutoResizeListView(ListViewName)

  

IEC 62552-3:2015 steady state SS1 iteration

A visualization for the iteration scheme to find steady state SS1 of refrigerator in energy consumption test according to IEC 62552-3:2015.

 

Excel VBA command button group

In this post I am going to explain an nice and handy trick to be used with Excel VBA. If you want to create a matrix of command buttons that each button will return a certain action when clicked, then this will be so useful for you. With this trick you can create calendars, calculators, and even non-editable data tables.

The trick requires the following to be done on MultiPage control:

[1] Setting tab style as button

[2] Setting MultiRow option to "True": so if the MultiPage control width is not enough to contain all tabs in single row, extra tabs will be placed to additional rows.

[3] Setting tab orientation from top, so tabs will populate from top-left to bottom-right

[4] Using fixed tab width and height

[5] Finally, setting the width and height of the Multipage control to values that just show the tabs without showing pages. You can use the following formulas to calculate the required width and height.

MultiPage control width=(Tab width+2) x No. of columns

MultiPage control height=(Tab height+2) x No. of rows

Where the value [2] is the preceding padding before each tab.

All the previous actions can be done using the properties window of the control as shown below.

Now, if you want to add buttons to MultiPage control dynamically you can follow the following simple code:

' Populate buttons on user-form show up Private Sub UserForm_Initialize() MultiPage1.Pages.Clear     'Clear all existing pages in the Multipage control ' Add 49 tabs (buttons) For i = 0 To 48     '0 is the index of first page MultiPage1.Pages.Add MultiPage1.Pages(i).Caption = CStr(i) Next i End Sub ' Setting the action Private Sub MultiPage1_Change() NoteLabel.Caption = "The pressed button is " + CStr(MultiPage1.Value) End Sub

To make it more spicy, the following picture shows an application for this trick to create a calendar control. You can download this demo from this link

Tags:

Easy way to create group of command buttons

The easiest/simplest way/method to create group of command buttons

Excel VBA the easiest way to create popup menu

Excel VBA create and populate popup menu

Excel VBA popup menu in userform

Excel VBA popup menu in worksheet

Excel VBA TabStrip trick

Excel VBA MultiPage trick

Excel VBA calendar userform

Excel VBA calendar in worksheet

Egypt regional humidity probability - Annex F - IEC 62552

The following data are based on weather data log of weather station of Cairo International Airport CAI HECA weather station number 623660 found in this link http://en.tutiempo.net/climate/ws-623660.html

The following table is approximate humidity probability Ri for Egypt during year 2016 to be used in Annex "F" in IEC 62552-3:2015. You can download the climate data of Cairo 2016 from this link https://drive.google.com/open?id=0BxLFp7fy6GivazZSanFDNDgxeEU

Assumptions:

[1] Measured temperatures are indoor

[2] Cairo weather is considered as Egypt weather

[3] 2017 weather is expected to be the same as 2016 weather

Year 2016
Relative humidity interval [%]	Nominal RH [%]	Regional humidity probability [%] at the following nominal dry bulb temperatures [°C]
		16 °C	22 °C	32 °C
]0,10]	5	0	0	0
]10,20]	15	0	0.28	1.11
]20,30]	25	0.28	2.49	2.76
]30,40]	35	1.38	5.53	3.87
]40,50]	45	6.08	9.67	6.35
]50,60]	55	9.39	8.84	19.06
]60,70]	65	6.91	7.74	4.97
]70,80]	75	1.93	1.38	0
]80,90]	85	0	0	0
]90,100]	95	0	0	0
Total probability		25.97	35.93	38.12

Top things to be done using access control units

In this post we introduce the  top things that can be done using access control units in work environment either they are used for check-in and check-out or for opening any door type for restricted area. These functions can be done only using access control units having the ability to identify or recognize the identity of the person through fingerprint, RFID tags or cards, or face detection and connected to a server that logs and takes actions.

[1] Top of the list, controlling the salary overtime and deductions. This is the traditional purpose for using check-in and check-out devices.

[2] Green spirit: Switch off lights, air conditioners, PCs, water heaters, water dispensers, and other devices when all employee in certain location had checked out. Also, it will turn on lights and devices once one employee had checked in.

[3] Project management: Update project schedules, Gantt charts, and projects critical paths in project management software based on the availability of human power.

[4] Extra security: Turn on door security alarm in restricted locations when all people had checked out

[5] Trace responsibility: For restricted access areas, and in the case of non-authorized person entering, if door is left opened, the responsible person is probably the last one opened the door.

[6] Meeting attendees management: Update MS Outlook or mail calendar for better management of meetings and conferences. If the person didn't check-in in the location, then it will be marked as not available in Outlook. 

[7] Meeting rooms management: Turn off the availability of meeting rooms if they have admins and all of the admins didn't check-in in the plant

[8] Reminders: Create on-arrival or on-departure reminder note for employee when he(she) checks in or out.

[9] Service management: In locations or plants preparing food for the employees, access control unit can be used for counting number of employees checked-in so a proper amount of food can be prepared so no food will be wasted.

[10] Emergency: Send automatic notification for emergency evacuation to mobile phones of all employee had checked-in in case of emergency (this can be a backup solution if emergency alarms didn't work).

[11] Data protection: Extra protection for PCs even if someone had entered the correct password of the PC. If this PC belongs to specific person, then it will not be unlocked until he(she) check-in.

[12] Safety check: Automatic safety check in case of bad weather conditions and explosions.

[13] Update mail address book: Alert IT to remove email of person who didn't check in for long time. This may be an indication that he/she left the company.

Tags:

Top uses of door access control unit

Top benefits of biometric access control device

Top applications of fingerprint access control unit

Non conventional uses of access control unit

Domestic refrigerator suction line accumulator

In refrigeration, suction line accumulator (sometimes called liquid trap) is a small tank that is used to deliver refrigerant gas to the compressor to avoid compressor flood-back, liquid refrigerant compression (in some cases), and the potential of external water vapor condensation on suction tube (if not insulated). Suction line accumulator is commonly placed between the exit of evaporator and the capillary-suction line heat exchanger. Suction line accumulator is a passive protection element (protect compressor) which is not functioning the whole time, but in certain situations it does its job.

Domestic refrigerators..., some have suction line accumulators and some don't. So, why this difference? To answer this question we have to ask the question:

What is the function of suction line accumulator?

As we said before, the accumulator function is to receive saturated mixture refrigerant (liquid+gas) and deliver a saturated vapor refrigerant gas. If the refrigerator is in the full-load state, the refrigerant state at the exit of evaporator (before accumulator) is probably saturated vapor and may be a superheated gas because the heat load is high enough to boil and evaporate the whole refrigerant inside the evaporator.

So, "What will happen in case of small loads?". At small loads the evaporator is flooded with liquid refrigerant and the exit of evaporator contains too much liquid refrigerant. The following are the cases where the liquid refrigerant return is possible:

[1] Overcharged refrigerator
[2] Oversized compressor: using compressor with higher cooling capacity
[3] Excessive accumulation of frost on evaporator which leads to thermal insulation of evaporator and evaporator clogging
[4] Stalled (stopped) evaporator fan
[5] Low ambient temperature
[6] Compressor startup
[7] Very small evaporator size
[8] Low evaporator heat exchange effectiveness

How suction line accumulator works?

Suction line accumulator has a tank and deflector tube. In this configuration the liquid refrigerant settles down in the bottom of tank. Also, the expansion of refrigerant in accumulator tank causes pressure drop which lower evaporation temperature and evaporates some liquid refrigerant as well. The deflector tube also splashes liquid refrigerant on accumulator walls causing more evaporation of liquid refrigerant and this is why most accumulators used in refrigerators are bare (non-insulated). Additionally, in frost-free refrigerators the defrost heater always evaporate the trapped liquid inside accumulator during the defrost period.

Commercial suction line accumulator. Source: Modern refrigeration and air conditioning

Suction line accumulators may be found in different orientations: horizontal, inclined, and vertical. The higher the inclination angle, the higher volume of liquid refrigerant it can trap.

Suction line accumulators are mostly used in refrigerators with single speed compressor with on-off control. Also they are plenty in refrigerators using refrigerant 134a as well as refrigerators having mechanical control.

Modern refrigerators with variable speed compressors or linear compressors can modulate different loads by controlling the refrigerant mass flow in the refrigeration circuit so it is not mandatory to use suction line accumulator. Most refrigerators with R600a, don't have suction line accumulators because the total amount of refrigerant inside the circuit is small compared to R134a.

Some designers don't use suction line accumulator as they rely on the heat of compressor motor which can evaporates the return liquid. Others may use long suction tube path outside the foam insulation to make sure that no liquid refrigerant will return.

How to calculate accumulator size (Accumulator sizing)?:

For a predefined refrigerant charge, the simplest and safest way is to assume that the whole refrigerant charge in refrigeration circuit is trapped inside accumulator in liquid state at evaporator pressure of ASHRAE conditions (evaporator temperature equals -23.3 C), calculate the density of liquid, and then the volume of this liquid (see the image below of an actual example)

Excel VBA leave command button event

Imagine that you have a command button that you want it to be highlighted with certain color when mouse pointer hovers (or moves above) it and restores its color when the mouse pointer leaves it.

In Excel VBA it is easy to detect if mouse pointer is inside the command button using the event handler  CommandButton1_MouseMove, but the opposite is not easy.

In Excel VBA there is no built-in event handler that handles the mouse pointer leave (exit) event of command button. For this reason I figured out this easy and tricky way to do this. The following method can work also with other control types.

The idea simply is to create an invisible padding area for the command button, so if the mouse pointer moved in this padding area then this means that the mouse pointer has leaved the button.

To do this we need to create a label and place a command button on top of it. The label will have larger size (width and height) than the command button so that there is padding around the button in all directions (see image below). It is preferred to have equal padding in all directions. For the label, clear the caption and set back color style to transparent so it will not be visible at all. Then, align middles and centers of the command button and the label. Optionally, you can group both the command button and the label so you can move them together easily without losing padding.

Now, when the mouse pointer moves inside the command button area this will simulate the enter event and when the mouse pointer moves inside the label area this will simulate the exit or leave event.

It is worthy to say that the larger the padding you use, the faster mouse moves you can handle.

Finally, it is time of coding, the following is a the shortest and simplest code ever:

Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 'Highlight button when enter command button CommandButton1.BackColor = &HFF00& End Sub   Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 'Restore button color when leave command button CommandButton1.BackColor = &H8000000F End Sub

In case you have large number of command buttons and you want to apply the same effect on them, you may use only one label that will be moved, resized, and brought on the back of the button after mouse pointer enters the button.

This post solve the following problems found in the links below:

Mouse Over Objects. Detect When Mouse Leaves
http://www.ozgrid.com/forum/showthread.php?t=62478

MouseMove - What is the reverse event?
http://stackoverflow.com/questions/12200618/mousemove-what-is-the-reverse-event

Mouse over command button VBA code
http://www.mrexcel.com/forum/excel-questions/552646-mouse-over-command-button-visual-basic-applications-code.html

how to detect when the mouse leaves a control on a userform
http://www.vbaexpress.com/forum/archive/index.php/t-21367.html