Problem Using If/Then Statements To Hide Columns

[helenabella]

Hi all,

I've been working on a print macro that allows the user to enter a
start date and an end date, and thereby automatically hide the
irrelevant columns on each worksheet when the sheet prints.

When the user selects a date from the two drop down menus, this puts a
number into ListBox3 and ListBox4 depending on their selection. The
macro then hides the columns before the start date and after the end
date, before printing the selected sheets and then reverting the
sheets to their former, unhidden state.

It's so close to working. It doesn't generate any errors when the
code runs, and it either hides the columns before the start date OR it
hides the columns after the end date. Unfortunately, it never manages
to do of these things both at once!

My testing has indicated that the problem occurs because the script is
alternately failing to recognise the value of ListBox3 ('D') or the
value of ListBox4 ('F') - see code below. Why it should only
recognise one at a time, and not even the same one for that matter, is
beyond me.

If anyone can make any suggestions towards resolving this, your wisdom
would be greatly appreciated!


____________________________________

Private Sub CommandButton1_Click()

' Forces correct selection of a date range to prevent errors.

If IsNull([ListBox3]) Then
MsgBox "Please enter a date range."
ElseIf IsNull([ListBox4]) Then
MsgBox "Please enter a date range."
Else

' Hides the UserForm to allow user to interact with the Print Preview
interface.

Me.Hide

' Defines stuff.

Dim Lst As String
Dim x As Long
Dim Sht As Worksheet
Dim D As Integer
Dim F As Integer

D = Val(ListBox3.Value)
F = Val(ListBox4.Value)

' Begins the sheet seletion routine.

For x = 0 To ListBox2.ListCount - 1
Lst = ListBox2.List(x)

' Hides Columns to comply with the selected Start Date.

If D = 2 Then
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Cover" And Sht.Name <> "Index" And
Sht.Name <> "Assumptions" And Sht.Name <> "Key" And Sht.Name <>
"Internal transaction inputs" Then
Sht.Range("F1:F1").EntireColumn.Hidden = True
End If
Next Sht
Sheets(Lst).Select
End If

.....and so forth down to...

If D = 60 Then
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Cover" And Sht.Name <> "Index" And
Sht.Name <> "Assumptions" And Sht.Name <> "Key" And Sht.Name <>
"Internal transaction inputs" Then
Sht.Range("F1:BL1").EntireColumn.Hidden = True
End If
Next Sht
Sheets(Lst).Select
End If


' Hides columns to comply with the selected End Date.

If F = 1 Then
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Cover" And Sht.Name <> "Index" And
Sht.Name <> "Assumptions" And Sht.Name <> "Key" And Sht.Name <>
"Internal transaction inputs" Then
Sht.Range("G1:BM1").EntireColumn.Hidden = True
End If
Next Sht
Sheets(Lst).Select
End If

....and so forth down to...

If F = 59 Then
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Cover" And Sht.Name <> "Index" And
Sht.Name <> "Assumptions" And Sht.Name <> "Key" And Sht.Name <>
"Internal transaction inputs" Then
Sht.Range("BM1:BM1").EntireColumn.Hidden = True
End If
Next Sht
Sheets(Lst).Select
End If

' Takes the seleted sheets to the Print Preview interface.

Select Case Sheets(Lst).Visible
Case Is = xlSheetVisible

Sheets(Lst).PrintOut preview:=False
End Select

Next

' Unhides the columns that were hidded for printing a specified date
range.

For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Cover" And Sht.Name <> "Index" And
Sht.Name <> "Assumptions" And Sht.Name <> "Key" And Sht.Name <>
"Internal transaction inputs" Then
Sht.Columns("F:BM").EntireColumn.Hidden = False
End If
Next Sht
Sheets(Lst).Select

' Unloads the form and returns the user to the Workbook.

Unload Me

End If

End Sub
______________________________________

 

[Helen]

Some further information: which ListBox Value is used to hide
columns, and which is ignored appears to be driven by the 'Locked:True/
False' Toggle.

ListBox3: Locked-True and ListBox4: Locked-True makes the ListBox3
conditional work and the ListBox4 conditional fail.
ListBox3: Locked-False and ListBox4: Locked-False makes the ListBox3
conditional fail and the ListBox4 conditional work.
ListBox3: Locked-True and ListBox4: Locked-False makes the ListBox3
conditional fail and the ListBox4 conditional work.
ListBox3: Locked-False and ListBox4: Locked-True makes the ListBox3
conditional work and the ListBox4 conditional fail.