Hiding Columns

[dmac1971]

Hi all. The vb below is driven by a selection box to hide columns. However once the columns reach the range DA and above they wont hide. I know this is simple to fix, but as I have been given the code by someone else I cant work it out. So much for comments eh?

Global VState As String
Global wsVC As Worksheet

Public Sub ViewChoose(ws As Worksheet)
frm_ViewChoose.Show
End Sub

Public Sub ShowView(strV As String)
With wsVC.Cells
Dim booSD As Boolean
Dim booSW As Boolean
Dim booSM As Boolean

If Left(strV, 1) = "S" Then
Select Case Right(strV, 1)
Case "D"
booSD = True
booSW = False
booSM = False
Case "W"
booSD = False
booSW = True
booSM = False
Case "M"
booSD = False
booSW = False
booSM = True
End Select
Else
booSD = False
booSW = False
booSM = False
If Mid(strV, 2, 1) = "D" Then
booSD = True
End If
If Mid(strV, 3, 1) = "W" Then
booSW = True
End If
If Mid(strV, 4, 1) = "M" Then
booSM = True
End If
End If




Dim intC As Integer
.Activate
For intC = 9 To wsVC.Range("A1").Value + 8

'For intC = 9 To 112
strcol = ColConvert(intC)
Select Case .Range(strcol & "1").Value
Case "D"
If booSD = False Then
.Columns(strcol & ":" & strcol).ColumnWidth = 0
Else
.Columns(strcol & ":" & strcol).ColumnWidth = 9
End If
Case "M"
If booSM = False Then
.Columns(strcol & ":" & strcol).ColumnWidth = 0
Else
.Columns(strcol & ":" & strcol).ColumnWidth = 12
End If
Case "W"
If booSW = False Then
.Columns(strcol & ":" & strcol).ColumnWidth = 0
Else
.Columns(strcol & ":" & strcol).ColumnWidth = 10
End If
End Select
Next intC
.Range("H17").Select
End With

End Sub

Public Function ColConvert(intC As Integer) As String
Select Case intC
Case 1 To 26
ColConvert = Chr(intC + 64)
Case 27 To 52
ColConvert = "A" & Chr(intC + 38)
Case 53 To 78
ColConvert = "B" & Chr(intC + 12)
Case 79 To 104
ColConvert = "C" & Chr(intC - 14)
Case 105 To 130
ColConvert = "D" & Chr(intC - 30)
Case 131 To 156
ColConvert = "E" & Chr(intC - 56)

End Select


End Function

[/quote]

 

[dmac1971]

Anyone?

 

[GlennUK]

For intC = 9 To wsVC.Range("A1").Value + 8

... what is the value in A1?

 

[dmac1971]

Sorry its 112. From Col I across there are column headings containing either D, W or M to show either a daily, weekly or montly result. The macro should show what ever the user requests and has worked fine until the results started using columns above DA.

 

[GlennUK]

It's 112 - so you wouldn't get anything happening past column DO then. But you are having trouble from column DA ... this code looks wrong:

Select Case intC 
Case 1 To 26 
ColConvert = Chr(intC + 64) 
Case 27 To 52 
ColConvert = "A" & Chr(intC + 38) 
Case 53 To 78 
ColConvert = "B" & Chr(intC + 12) 
Case 79 To 104 
ColConvert = "C" & Chr(intC - 14) 
Case 105 To 130 
ColConvert = "D" & Chr(intC - 30) 
Case 131 To 156 
ColConvert = "E" & Chr(intC - 56)

as you can see -30 is 16 less than -14 instead of 26 less. Change it to be 40 instead. You need to change -56 to -66 too.