How to fine-tune the boxes in excel table

How to fine-tune the boxes in the excel table

I recommend a method, using VBA. Assume that the shape is in sheet1 and is named Rectangle 1. The following settings are exactly equal, and can also be set according to percentages, etc. If setting in batches, a loop can be used.

1

2

3

4

worksheets(1).shapes("rectangle 1").left=worksheets(1).cells(1,1).left

worksheets(1).shapes("rectangle 1").top=worksheets(1).cells(1,1).top

worksheets(1).shapes("Rectangle 1").width=worksheets(1).cells(1,1).width

worksheets(1).shapes("Rectangle 1").height=worksheets(1).cells(1,1).height

Excel EXCEL How to set functions in numerical adjustment buttons

It seems that you are using the [Digital Adjustment Knob] in the [Control Toolbox]

Of course you need to set the link when setting. For example, if you want to display the results in A1, fill in after [LinkedCell]: A1

It is recommended that you use [Spinner] in [Form], the settings here are more intuitive!

Of course, you also need to set [Cell Link], which is the cell in which you want to display the data. For example, if you want to display it in A1, fill in [Cell Link]: A1

As for the [multiple step sizes] problem you mentioned, we can use VBA to program it in the control event of the [Digital Adjustment Knob]!

You can also "find another way" and use formulas to solve it! Take the method of [fine-tuning items] as an example:

Right click [Set Control Format] [Control]:

Minimum value: 0

Maximum value: 51

Step size: 1

Cell link: Z1

Sure

We write the formula in cell A1:

=IF(Z1=0,0,(Z1 9)*100)

How to create dynamic table headers in Excel to improve work efficiency

If you can make a dynamic header, make all the parts of the title that need to be changed into cell references. As long as you change one place, all the titles will change together, which is much more convenient. Later, after trying to achieve this goal:

1. Create table title initialization interface

For the convenience of operation, we first insert a new worksheet and name it "Initialization". Enter the text prompt "Please enter the unit name:" in cell C7, so that cell D7 can be used to enter the unit name.

Next, right-click the toolbar and select the "Form" command to open the "Form" toolbar. Enter "Time:" in cell C9, and cell D9 is used to display the year in the title. Click the [Spinner] button in the "Form" toolbar and draw a spinner object immediately behind cell D9. Similarly, draw a spinner box immediately behind cell F9 to adjust the month in the table title.

Enter the words "year" and "month" in cells E9 and G9 respectively, and set them to right alignment.

Right-click "Spinner 1" and select the "Set Control Format" command. The "Object Format" dialog box will open and switch to the "Control" tab. Enter a default year such as "2005" in the "Current value" box, enter the upper and lower limits of the year in the "Minimum value" and "Maximum value" boxes, "Step size" is "1", and in the "Cell link ” box, enter the absolute address of the year cell to be displayed, such as “$D$9”. After the setting is completed, click the [OK] button, so that spinner 1 and cell D9 are linked. Similarly, you can link "Spinner 2" and cell F9. Note that the maximum and minimum values ​​are limited by the month itself.

2. Set table title

Open the "Overtime Record" worksheet, enter the formula "=Initialization! D7&Initialization! D9&"Year"&Initialization!F9&"Month"&"Overtime Record"" in the title position of the table, and press the Enter key ( The final effect cannot be seen at this time), set the format of the title. Similarly, the titles of other tables can be made one by one with just a few changes.

3. Debugging table title

After the above operations are completed, we can start debugging. Return to the "Initialization" table, enter the school name and select a certain year and month. At this time, return to other worksheets and we can see the complete table title.

It should be noted that the date function can also be used to automatically change the title according to the system time, but it is not convenient to adjust to a previous year or month.

The above is the detailed content of How to fine-tune the boxes in excel table. For more information, please follow other related articles on the PHP Chinese website!