Here is a class module for that, primary designed for Userforms, but should also work for your scenario. column size, shrink the frameĪnd move the labels to the appropriate position and finally resize the cell.Īs you can suspect from this brief description you have to write a When you want to have a "Autofit" feature, you have to fill the captions into the labels, set the AutoSize property of each label to True, determine the width and height of each label, calculate the min. To link a text box to a cell, have the text box selected, click in the Formula Bar and press and then click the cell to link to and press Enter see Figure 02.
Then use the mouse to draw the text box above the sheet grid. Right click the command button (make sure Design Mode is selected). Drag a command button and a text box on your worksheet.
In the ActiveX Controls group, click Text Box.
The code fit the labels to the cell, it depends on the size of the cell how large the labels becomes.īelow is a sample to add the labels to a frame. To insert a text box, click the Insert ribbon and click the Text Box icon on the far right. To create a text box in Excel VBA, execute the following steps. Set Lbl = ("Forms.Label.1", "lbl1")Ĭode do not autofit the labels into the cell. Set Myframe = LblSht.OLEObjects("Frame1").Object "ame.1", Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.Height I have below code to add frame and 1 label to a frame. Each label caption shall come from seperate sheet "Ref sheet" range A1 to A6 and value for each textboxes shall come from ref sheet rangeĪlso, cell B17 shall expand to autofit the frame, and labels and textboxes shall autofit into the frame. I want to add frame on the sheet with 6 labels and 6 textboxes.
You will feel good to hear that adding ActiveX Controls to a worksheet is easy. I want to make slight change in the code. Learn how to use ActiveX Controls in Excel. Code do not autofit the labels into the cell. Thanks for the code, it works great but have to increase the size of the cell manually. In order to insert a Spin button in the Worksheet, you need to go to the Developer tab, click Insert and under ActiveX. Having to convert all my readable references such as tblLoansBalance via vba and seeing datae2:e1000 instead was visually painful and the idea of maintaining such a document is no longer conceivable.
Below is a sample using the ControlPositioner class. Dealing with unintended Excel Active-X resizing quirks (VBA code simulates 'self correction') David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls readily available to the Excel user from the Developer Ribbon, or the Controls ToolBox). I had to send an excel file to a client today who has Sheets but not Excel and was horrified to discover Sheets does not process tables. Excel vba VBA text box font color I'm helping update Excel templates at my work and I need some help.