Hiding Columns

dmac1971

New Member
Joined
Oct 3, 2006
Messages
6
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]
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

dmac1971

New Member
Joined
Oct 3, 2006
Messages
6
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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:
Code:
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.
 

Forum statistics

Threads
1,143,637
Messages
5,719,972
Members
422,253
Latest member
frankie2016tata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top