Nov 14 2022 09:49 PM
please refer to the video and excel sheet.
is it possible to create the like that the video.
Nov 15 2022 01:23 AM
See the attached version. You will have to allow macros.
Right-click the sheet tab and select 'View Code' to inspect the VBA code.
Nov 20 2022 09:29 PM
Nov 21 2022 12:47 AM
SolutionAfter downloading the workbook, right-click it in Windows Explorer and select Properties from the context menu. If you see Unblock, click it, then click OK.
You may also want to make the folder containing the workbook a trusted location for Excel (File > Options > Trust Center > Trust Center Settings... > Trusted Locations)
Nov 21 2022 03:29 AM
Nov 21 2022 05:49 AM
Right-click the sheet tab and select 'View Code' from the context menu.
Change the Worksheet_Change event procedure to
Private Sub Worksheet_Change(ByVal Target As Range)
Const MaxRows = 10
Dim NumRows As Long
If Not Intersect(Range("D2"), Target) Is Nothing Then
Application.ScreenUpdating = False
Range("C6:E" & MaxRows + 5).Clear
NumRows = Val(Range("D2").Value)
If NumRows < 1 Then Exit Sub
If NumRows > MaxRows Then
MsgBox "The maximum is " & MaxRows, vbExclamation
Exit Sub
End If
Range("C6:E" & NumRows + 5).Borders.LineStyle = xlContinuous
Range("E6:E" & NumRows + 5).Formula = "=D6-C6"
Application.ScreenUpdating = True
End If
End Sub
Nov 25 2022 02:37 AM - edited Nov 25 2022 02:38 AM
Nov 25 2022 02:37 AM - edited Nov 25 2022 02:38 AM
it is working,
While adding the rows or delete rows the deviation (D6-C6) Values is disappearing.
Nov 25 2022 04:46 AM
New version:
Private Sub Worksheet_Change(ByVal Target As Range)
Const MaxRows = 10
Dim NumRows As Long
Dim LastRow As Long
If Not Intersect(Range("D2"), Target) Is Nothing Then
Application.ScreenUpdating = False
LastRow = Range("E" & MaxRows + 6).End(xlUp).Row
NumRows = Val(Range("D2").Value)
If NumRows < 1 Then Exit Sub
If NumRows > MaxRows Then
MsgBox "The maximum is " & MaxRows, vbExclamation
Exit Sub
End If
If LastRow > NumRows + 5 Then
Range("C" & NumRows + 6 & ":E" & LastRow).Clear
End If
Range("C6:E" & NumRows + 5).Borders.LineStyle = xlContinuous
Range("E6:E" & NumRows + 5).Formula = "=D6-C6"
Application.ScreenUpdating = True
End If
End Sub
Nov 21 2022 12:47 AM
SolutionAfter downloading the workbook, right-click it in Windows Explorer and select Properties from the context menu. If you see Unblock, click it, then click OK.
You may also want to make the folder containing the workbook a trusted location for Excel (File > Options > Trust Center > Trust Center Settings... > Trusted Locations)