PDA

View Full Version : Solved: If Cell is certain value THEN hide rows



tomsweddy
06-22-2009, 03:50 AM
Could anyone help me with the following statement that I need to create.

What I need VB to do in my Excel workbook is as follows;


IF Sheet1!K10 = "Permanent"

THEN

Hide rows 24 - 27 on Sheet2

ELSE

Keep rows 24 - 27 on Sheet2 visible



Could anyone suggest some code for me to be placed in a sub which will be acitivated by a button click?

Thanks alot,
Tom

georgiboy
06-22-2009, 04:01 AM
Welcome to the forum :hi:

You could try something like this...
With Sheet2.Rows("24:27").EntireRow
Select Case Sheet1.Range("K10").Value
Case "Permanent": .Hidden = True
Case Else: .Hidden = False
End Select
End With
Hope this helps

Bob Phillips
06-22-2009, 05:27 AM
or more succinctly



Sheet2.Rows("24:27").Hidden = Sheet1.Range("K10").Value = "Permanent"

tomsweddy
06-22-2009, 06:18 AM
XLD,

I am trying to do this your way but not sure the syntax is completely correct.... I am getting a compiler error with "Expected: (" where the INPUT bit is in the statement.

(INPUT is the name of my Sheet1)

Here is the code built into my sub

Sub FormatAndPreview()
Dim sSheetName As String

On Error Resume Next
sSheetName = Application.VLookup(Range("K12").Value, Range("F119:G127"), 2, False)
On Error GoTo 0

If sSheetName <> "" Then

With Sheets(sSheetName)

.Rows("24:27").Hidden = INPUT!.Range("K10").Value = "Permanent"


.PrintPreview
End With

Else
MsgBox "You have not selected a Contract Type", vbOKOnly + vbInformation, "Information"
End If




End Sub

tomsweddy
06-22-2009, 06:19 AM
XLD, I am trying to do this your way but getting a compiler message error saying "Expected: (".....

Here is your code copied into my sub

(INPUT is now the name of my sheet1)

Sub FormatAndPreview()
Dim sSheetName As String

On Error Resume Next
sSheetName = Application.VLookup(Range("K12").Value, Range("F119:G127"), 2, False)
On Error GoTo 0

If sSheetName <> "" Then

With Sheets(sSheetName)

.Rows("24:27").Hidden = INPUT.Range("K10").Value = "Permanent"


.PrintPreview
End With

Else
MsgBox "You have not selected a Contract Type", vbOKOnly + vbInformation, "Information"
End If




End Sub

Bob Phillips
06-22-2009, 06:23 AM
It is hard to be sure, but probably the sheet name is INPUt, but not the sheet codenmae.

Try



Sub FormatAndPreview()
Dim sSheetName As String

On Error Resume Next
sSheetName = Application.VLookup(Range("K12").Value, Range("F119:G127"), 2, False)
On Error GoTo 0

If sSheetName <> "" Then

With Sheets(sSheetName)

.Rows("24:27").Hidden = Worksheets("INPUT").Range("K10").Value = "Permanent"

.PrintPreview
End With

Else
MsgBox "You have not selected a Contract Type", vbOKOnly + vbInformation, "Information"
End If
End Sub

tomsweddy
06-22-2009, 06:31 AM
Thanks, made the change and the syntax goes through fine. However, when I run the command it doesn't seem to hide the rows?

As you can see from your original code....

Sheet2.Rows("24:27").Hidden = Sheet1.Range("K10").Value = "Permanent"

I have deleted the Sheet2 because I figured that the code in the sub (If sSheetName <> "" Then ) already should pick up the correct sheet and do the functions as asked to. I can confirm that it Print Previews the correct sheet, but as I said it doesnt seem to hide the rows...

Sub FormatAndPreview()
Dim sSheetName As String

On Error Resume Next
sSheetName = Application.VLookup(Range("K12").Value, Range("F119:G127"), 2, False)
On Error Goto 0

If sSheetName <> "" Then

With Sheets(sSheetName)

.Rows("24:27").Hidden = Worksheets("INPUT").Range("K10").Value = "Permanent"

.PrintPreview
End With

Else
MsgBox "You have not selected a Contract Type", vbOKOnly + vbInformation, "Information"
End If
End Sub

any thoughts on how to fix?

Thanks alot!

Bob Phillips
06-22-2009, 07:15 AM
It works fine for me.

You have to be careful when using implicit addresssing. The Vlookup is going to lookup the value in K12 of the activesheet, and into the table F119:G127 of the activesheet. That may be the root of yur problems.