Use a VBA Variable as the Maximum Value in WPS Spreadsheets Data Validation
Question details
The user needs to use a VBA variable as the Formula1 maximum value for a decimal data validation rule and automatically rebuild the validation when the source value changes.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working in a worksheet where the merged N3:O3 range supplies the maximum value for a decimal data validation rule.
- Observed behavior
- The validation accepts a variable maximum only when the rule is deleted and re-added through a Worksheet_Change event placed in the worksheet module; placing the code in a standard module does not trigger the event.
Open the workbook in WPS Spreadsheets and save it as a macro-enabled file so the VBA event is retained. Confirm that you can access the Visual Basic editor with Alt+F11 and that the Developer tab is available if you prefer to use the ribbon.
Rebuild the Decimal Validation from a Worksheet_Change Event
Use this method when the maximum value is stored in the merged N3:O3 range and must update automatically whenever that cell changes.
Formula1 is read-only at runtime, so you cannot simply assign a new variable to an existing validation. The reliable approach is to delete the existing rule and create it again inside the worksheet-level change event.
Right-click the sheet tab that contains N3:O3 and choose View Code. If the Visual Basic editor is already open, double-click that worksheet in the Project Explorer instead of inserting a standard module.
In the code window, select Worksheet from the left Object dropdown and Change from the right Procedure dropdown. This creates Private Sub Worksheet_Change(ByVal Target As Range).
Add code inside the event: If Not Intersect(Target, Me.Range("N3:O3")) Is Nothing Then Dim TktAvg As Double: TktAvg = CDbl(Target.Value): Me.Range("A2:A100").Validation.Delete. Adjust A2:A100 to the validated range.
In the same block add: Me.Range("A2:A100").Validation.Add Type:=xlValidateDecimal, Operator:=xlLess, Formula1:=TktAvg. This passes the VBA variable as the Formula1 maximum.
Save the workbook as a macro-enabled file, change the value in N3:O3, and confirm that the data validation limit updates for the target range.
Force the Correct Numeric Type with CLng or CDbl
Use this when your comparison must be an integer or a decimal and you want to prevent type mismatch errors before passing the variable to Formula1.
Use a VBA Variable Maximum for Dynamic Data Validation in WPS Spreadsheets
WPS Spreadsheets supports Excel VBA-compatible Worksheet_Change code, so you can delete and re-add a decimal validation rule with Formula1 set to a variable and make the maximum update automatically.
- 1. Open WPS Spreadsheets and launch the VBA editor: Press Alt+F11 to open the Visual Basic editor in WPS Spreadsheets.
- 2. Select the relevant worksheet module: In Project Explorer, double-click the worksheet that contains N3:O3 so the event code is placed in the worksheet module, not a standard module.
- 3. Add the Worksheet_Change code: In the Worksheet_Change event, check Target against N3:O3, delete the old validation, and add a decimal less-than rule with Formula1:=TktAvg.
- 4. Save as a macro-enabled workbook: Save the file in the macro-enabled .xlsm format to preserve the VBA code and validation behavior.
- 5. Test the dynamic maximum: Edit the N3:O3 value and confirm that the target data validation maximum changes automatically.
Frequently Asked Questions
Why does my VBA variable not apply to the existing data validation?
The Formula1 property is read-only after the rule is created. Delete the existing validation with Validation.Delete and add a new decimal rule with Formula1:=TktAvg inside the Worksheet_Change event.
Where should I put the Worksheet_Change procedure?
Right-click the sheet tab that contains N3:O3, choose View Code, and place the code in that worksheet module. Do not put it in a standard module inserted from the VBA editor.
How do I handle a merged N3:O3 maximum cell?
Use If Not Intersect(Target, Me.Range("N3:O3")) Is Nothing Then and read Target.Value from the merged range. Then rebuild the validation for the target range.
Should I use CLng or CDbl for the maximum value?
Use CDbl when the maximum can be a decimal and CLng when you need an integer limit. Convert the Target.Value before you pass it as Formula1.
Does this work when the file is opened in Microsoft Excel?
Yes, if you save the workbook in the macro-enabled .xlsm format, the Worksheet_Change event and validation rule remain compatible with Microsoft Excel.
