LordDragon
08-06-2015, 05:36 PM
Greetings,
I have a small dilema with a code to hide or unhide rows in a sheet.
On my Project Info sheet, I have a ComboBox for the user to select if the system uses Imperial or Metric. We only have one system (the PVT) where this is hardwired in and it therefore makes a difference which they choose. So I want to prevent accidental ordering of the wrong parts.
The rest of the workbook will Hide the rows they should not be ordering based on the given criteria. Typically, that criteria only deals with the voltage of the system (110V or 220V). So I put the code to hide or unhide the rows based on that criteria in the Worksheet_Activate() section.
However, with the Metric/Imperial problem, there is an Imperial system for both 110V and 220V and a Metric for each as well.
I have put the following code in so when the PVT is selected, then if the ComboBox used to select the Units (UnitsBox) says "Imperial" then it will run one code. If the UnitsBox says "Metric", it runs a different code. This part works.
Private Sub PVTBox_Change()
'Hides or Unhides the right sheet
If LCase(PVTBox.Value) = "yes" And Worksheets("PVT").Visible = False Then
Worksheets("PVT").Visible = True
End If
If LCase(PVTBox.Value) = "yes" And LCase(UnitsBox.Value) = "imperial" Then PVTUnhideI
If LCase(PVTBox.Value) = "yes" And LCase(UnitsBox.Value) = "metric" Then PVTUnhideM
If LCase(PVTBox.Value) = "no" And Worksheets("PVT").Visible = True Then
Worksheets("PVT").Visible = False
End If
End Sub
It's the Functions that are called that are the problem.
Function PVTUnhideI()
'Automatically sets the Required equipment to "Yes" and sets the default value for each. These can of course be changed.
'Declare the variables
Dim strYes As String
Dim strNo As String
strYes = "Yes"
strNo = "No"
'Ensure the PVT sheet is selected.
With ActiveWorkbook.Worksheets("PVT")
'Set the Required equipment to Yes.
.Range("A4").Value = strYes
'Set the default quantity for each item on the list
.Range("E4").Value = 1
'Hide & Lock the rows that are Metric equipment. Unhide & unlock the rows that are Imperial
.Rows("11").EntireRow.Hidden = False
.Range("A11").Locked = False
.Range("E11").Locked = False
.Range("A12").Locked = True
.Range("A12").Value = strNo
.Range("E12").Locked = True
.Range("E12").ClearContents
.Rows("12").EntireRow.Hidden = True
.Rows("13").EntireRow.Hidden = False
.Range("A13").Locked = False
.Range("E13").Locked = False
.Range("A14").Locked = True
.Range("A14").Value = strNo
.Range("E14").Locked = True
.Range("E14").ClearContents
.Rows("14").EntireRow.Hidden = True
End With
End Function
When I run this code, I get an error (the Debug box pops up) and it highlights the Hide Rows part. If I comment out the Hide Rows part, the rest works. I don't understand why.
Note: The PVTUnhideM code is exactly the same as the above code, just reverse which cells are hidden or locked.
Thanks.
I have a small dilema with a code to hide or unhide rows in a sheet.
On my Project Info sheet, I have a ComboBox for the user to select if the system uses Imperial or Metric. We only have one system (the PVT) where this is hardwired in and it therefore makes a difference which they choose. So I want to prevent accidental ordering of the wrong parts.
The rest of the workbook will Hide the rows they should not be ordering based on the given criteria. Typically, that criteria only deals with the voltage of the system (110V or 220V). So I put the code to hide or unhide the rows based on that criteria in the Worksheet_Activate() section.
However, with the Metric/Imperial problem, there is an Imperial system for both 110V and 220V and a Metric for each as well.
I have put the following code in so when the PVT is selected, then if the ComboBox used to select the Units (UnitsBox) says "Imperial" then it will run one code. If the UnitsBox says "Metric", it runs a different code. This part works.
Private Sub PVTBox_Change()
'Hides or Unhides the right sheet
If LCase(PVTBox.Value) = "yes" And Worksheets("PVT").Visible = False Then
Worksheets("PVT").Visible = True
End If
If LCase(PVTBox.Value) = "yes" And LCase(UnitsBox.Value) = "imperial" Then PVTUnhideI
If LCase(PVTBox.Value) = "yes" And LCase(UnitsBox.Value) = "metric" Then PVTUnhideM
If LCase(PVTBox.Value) = "no" And Worksheets("PVT").Visible = True Then
Worksheets("PVT").Visible = False
End If
End Sub
It's the Functions that are called that are the problem.
Function PVTUnhideI()
'Automatically sets the Required equipment to "Yes" and sets the default value for each. These can of course be changed.
'Declare the variables
Dim strYes As String
Dim strNo As String
strYes = "Yes"
strNo = "No"
'Ensure the PVT sheet is selected.
With ActiveWorkbook.Worksheets("PVT")
'Set the Required equipment to Yes.
.Range("A4").Value = strYes
'Set the default quantity for each item on the list
.Range("E4").Value = 1
'Hide & Lock the rows that are Metric equipment. Unhide & unlock the rows that are Imperial
.Rows("11").EntireRow.Hidden = False
.Range("A11").Locked = False
.Range("E11").Locked = False
.Range("A12").Locked = True
.Range("A12").Value = strNo
.Range("E12").Locked = True
.Range("E12").ClearContents
.Rows("12").EntireRow.Hidden = True
.Rows("13").EntireRow.Hidden = False
.Range("A13").Locked = False
.Range("E13").Locked = False
.Range("A14").Locked = True
.Range("A14").Value = strNo
.Range("E14").Locked = True
.Range("E14").ClearContents
.Rows("14").EntireRow.Hidden = True
End With
End Function
When I run this code, I get an error (the Debug box pops up) and it highlights the Hide Rows part. If I comment out the Hide Rows part, the rest works. I don't understand why.
Note: The PVTUnhideM code is exactly the same as the above code, just reverse which cells are hidden or locked.
Thanks.