PDA

View Full Version : [SOLVED] Hiding Rows from a Function



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.

Kenneth Hobs
08-07-2015, 06:28 AM
Change Function to Sub.

LordDragon
08-07-2015, 12:32 PM
Thanks for the suggestion. I tried that, but I'm still having the same problem.

I tried putting them in a Module and in the Project Info page and got the same Debug box.

I tried putting them in the PVT page and it said there was no such Function or Sub.

Kenneth Hobs
08-07-2015, 01:35 PM
Maybe you can attach a simple example file.

I don't know if your Private Sub would go into a Sheet object or a Userform. I don't use ActiveX controls in sheets anymore.

The Function changed to a Sub would go in a Module.

Even so, if you have not unprotected the sheet it is changing, you will have issues. I normally set all worksheets protection password with UserInterface:=True in ThisWork's Open event. I do this so that code can make changes.

You should not need to Select the worksheet.

LordDragon
08-07-2015, 01:56 PM
Here is a sample of my file. I don't know how to upload it to this site, so I put it elsewhere online. Here is the link though. http://1drv.ms/1EekgQl

I used the UserInterface:= True thing (like you had suggested in another post), so the workbook is protected, but the code is still allowed to work.

I'm having it select the worksheet because even though I know I shouldn't have to, I've had some of my code accidentally write to the wrong worksheet. So I put that in there as a fail-safe.

Kenneth Hobs
08-07-2015, 02:13 PM
That is not a valid link. To attach a file, in a reply, click the Go Advanced button in lower right of reply box. Select the paperclip icon to browse to your file.

LordDragon
08-07-2015, 02:30 PM
Cool. Here you go then.

Kenneth Hobs
08-07-2015, 05:15 PM
What should be changed in your file to produce the problem? I ran both routines but no problem occurred.

LordDragon
08-07-2015, 07:34 PM
When I change the PVTBox to Yes on the Project Info page. The PVT page displays and the Debug box pops up.

14103

Kenneth Hobs
08-07-2015, 08:17 PM
Again:

Even so, if you have not unprotected the sheet it is changing, you will have issues. I normally set all worksheets protection password with UserInterface:=True in ThisWork's Open event. I do this so that code can make changes.

In this Workbook object, paste this and change ken to your password and add any other protection options.

Private Sub Workbook_Open() Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect "ken", UserInterfaceOnly:=True
Next ws
End Sub

LordDragon
08-08-2015, 07:44 PM
Thanks Ken, that's exactly the code I've been using to protect the workbook. The only difference is I changed the variable name from "ws" to "wkSheet".

I think I have a new solution to my problem. I'm going to attempt it. I'll post the result here when I'm done testing it.

LordDragon
08-11-2015, 04:53 PM
As promised, here is the final solution.

I deleted two of the four rows that I had my parts in. Then I created a Sub that would look to my UnitsBox and rewrite the cells associated with the parts that cared if the system was Metric or Imperial.



Private Sub UnitsBox_Change()
'Provides or Removes the Metric or Imperial items based on the selection.


If LCase(UnitsBox.Value) = "imperial" Then 'UnitsImperial
With ActiveWorkbook.Worksheets("PVT")
.Range("C11").Value = "PVT Monitor Assembly 110V Imperial Unit"
.Range("D11").Value = "PVTASS008"
.Range("C12").Value = "PVT Monitor Assembly 220V Imperial Unit"
.Range("D12").Value = "PVTASS009"
End With
End If


If LCase(UnitsBox.Value) = "metric" Then 'UnitsMetric
With ActiveWorkbook.Worksheets("PVT")
.Range("C11").Value = "PVT Monitor Assembly 110V Metric Unit"
.Range("D11").Value = "PVTASS005"
.Range("C12").Value = "PVT Monitor Assembly 220V Metric Unit"
.Range("D12").Value = "PVTASS006"
End With
End If

End Sub


Then I used the same style coding I was using for everything else that was concerned about Voltage and put that in the Worksheet_Activate module. So if the system is 110V everything that is 220V is locked, cleared and hidden. It reverses that if the system is 220V.

Thanks for the help Ken.