Hi there
I have created the code below finding bits and pieces from this forum. I have done coding before but I am relatively new to VBA. Code was working relatively fine until I have introduced this section.
What I want to achieve is to have a fixed value (1) when "DC" is selected, and manual entry the value when is not. Code delivers the output but looks like computations are overloaded as it takes around 5s for Excel to revive from the trauma of reading the code . Any advice on why is this happening and how to improve the above section or the code in general. Any answers will be much appreciated. Cheers
I have created the code below finding bits and pieces from this forum. I have done coding before but I am relatively new to VBA. Code was working relatively fine until I have introduced this section.
VBA Code:
If .Range("Volt").Value = "DC" Then
.Range("pf").Formula = "1"
End If
What I want to achieve is to have a fixed value (1) when "DC" is selected, and manual entry the value when is not. Code delivers the output but looks like computations are overloaded as it takes around 5s for Excel to revive from the trauma of reading the code . Any advice on why is this happening and how to improve the above section or the code in general. Any answers will be much appreciated. Cheers
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
With Sheets("Input & Results")
'-----Change visibility of check boxes--------------'
If .Range("CorTyp").Value = "Multicore" Then
.CheckBoxes("CheckBoxSc").Visible = True
Else
.CheckBoxes("CheckBoxSc").Visible = False
End If
If (.Range("CorTyp").Value = "Multicore") Or (.Range("InsCon").Value = "Unenclosed - Spaced from surface") Or (.Range("Volt").Value = "DC") Then
.CheckBoxes("CheckBoxSpa").Visible = False
.CheckBoxes("CheckBoxSpa").Value = False
Else
.CheckBoxes("CheckBoxSpa").Visible = True
End If
If .Range("CorMat").Value = "Al" Then
.CheckBoxes("CheckBoxTC").Visible = False
.CheckBoxes("CheckBoxTC").Value = False
Else
.CheckBoxes("CheckBoxTC").Visible = True
'.CheckBoxes("CheckBoxTC").Value = True
End If
'-------Assign a fix value to pf cell in when Voltage is DC-----------'
[B]If .Range("Volt").Value = "DC" Then
.Range("pf").Formula = "1"
End If[/B]
'-------Clear cells if dropdown values are changed-----------'
If Not Application.Intersect(Target, Range("Volt")) Is Nothing Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
.Range("SupVol").ClearContents
.Range("ConCon").ClearContents
End If
End If
If Not Application.Intersect(Target, Range("CorTyp")) Is Nothing Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
.Range("InsCon").ClearContents
.Range("ArrCir").ClearContents
.Range("ConCon").ClearContents
End If
End If
If Not Application.Intersect(Target, Range("InsCon")) Is Nothing Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
.Range("ArrCir").ClearContents
.Range("NoC").ClearContents
End If
End If
If Not Application.Intersect(Target, Range("ArrCir")) Is Nothing Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
.Range("NoC").ClearContents
End If
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub
End With