Many columns
 

Hi Guys,

I have data within columns a to d which is static.
From Columns e onwards to say az there is a variety of information which
needs to be entered on a row by row basis. It is rather time consuming
to write macros to hide and unhide columns depedning on the view
required. Is there any easier way of group (ie viewing) columns? (eg I
want to see columns a - d, and h - k, for purposes of data entry 1, and
then to enter data set 2, I need to see A - d and m - q), and all
others hidden).

Kind Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

Many columns
 

In my old job, I had a similar problem. Although it may be rather kludge-y,
we ended up highlighting the various "views" with different background
colors. Then I wrote a macro to hide or unhide rows and columns depending on
the background color of the cell that was currently selected when the macro
was run.

In our situation, the different scenarios headers were found somewhere in
rows 1 to 15, and the data entry started on row 16. You may need to adjust
this code slightly to better fit your need. Hope it helps.

Public Sub DoTemplate()

Dim objWS As Excel.Worksheet
Dim objCell As Excel.Range, objR As Excel.Range
Dim I As Byte, K As Byte
Dim J As Long

Set objWS = ActiveWorkbook.ActiveSheet
Set objCell = Application.Selection

K = objCell.Row
J = objCell.Interior.ColorIndex

' Hide rows
For I = 2 To 15

If I < K Then

objWS.Rows(I).Hidden = True

End If

Next

' Hide columns
For I = 1 To 167

Set objR = objWS.Cells(K, I)
If objR.Interior.ColorIndex < J Then

objWS.Columns(I).Hidden = True

End If

Next

Set objR = Nothing
Set objCell = Nothing
Set objWS = Nothing

End Sub
--
Hmm...they have the Internet on COMPUTERS now!


"Darin Kramer" wrote:



Hi Guys,

I have data within columns a to d which is static.
From Columns e onwards to say az there is a variety of information which
needs to be entered on a row by row basis. It is rather time consuming
to write macros to hide and unhide columns depedning on the view
required. Is there any easier way of group (ie viewing) columns? (eg I
want to see columns a - d, and h - k, for purposes of data entry 1, and
then to enter data set 2, I need to see A - d and m - q), and all
others hidden).

Kind Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

Many columns
 

Thanks - it may work :) - Im playing now... but if I want it to run on
the sheet called master, do i need to change this line:

Set objWS = ActiveWorkbook.ActiveSheet

What would the syntax be...?

Thanks, appreciate your assistance.

Kind Regards

D




*** Sent via Developersdex http://www.developersdex.com ***

Many columns
 

hmm...Im almost there, (but as u can tell am no programmer!), could I
have a bit more assistance.... in telling macro only to look at row 2's
background colour (i dont want to highlight all the backround colours of
the whole column....)

What i have so far is:

Public Sub DoTemplate()


Dim objWS As Excel.Worksheet
Dim objCell As Excel.Range, objR As Excel.Range
Dim I As Byte, K As Byte
Dim J As Long

Set objWS = Sheets("master")
Set objCell = Application.Selection

K = objCell.Row
J = objCell.Interior.ColorIndex

' Hide rows
'For I = 2 To 2

' If I < K Then

' objWS.Rows(I).Hidden = True

' End If

'Next

' Hide columns

For I = 2 To 52

Set objR = objWS.Cells(K, I)
If objR.Interior.ColorIndex < J Then

objWS.Columns(I).Hidden = True

End If




*** Sent via Developersdex http://www.developersdex.com ***

Many columns
 

If you're not looking at the sheet "Master" when you run the macro, then you
will need to change that line to something like:

Set objWS = Activeworkbook.Worksheets("Master")

(That would work even if you WERE looking at "Master" as well.)

--
Hmm...they have the Internet on COMPUTERS now!


"Darin Kramer" wrote:


Thanks - it may work :) - Im playing now... but if I want it to run on
the sheet called master, do i need to change this line:

Set objWS = ActiveWorkbook.ActiveSheet

What would the syntax be...?

Thanks, appreciate your assistance.

Kind Regards

D




*** Sent via Developersdex http://www.developersdex.com ***

Many columns
 

OK, if you want everything to be in row 2, then we can change a few things.
Note the comments I added within the code itself.

Public Sub DoTemplate()

Dim objWS As Excel.Worksheet
Dim objCell As Excel.Range, objR As Excel.Range
Dim I As Byte
Dim J As Long

Set objWS = Sheets("Master")
' Don't forget that wherever you're looking at, the selected cell needs to
have a background
' color that matches the various "views" you've built on the master sheet.
Perhaps
' have a chart, where users click a cell that has a "view" name, and that
cell has a
' background color corresponding to the cells on the master sheet.
Set objCell = Application.Selection

J = objCell.Interior.ColorIndex

' Hide columns
' If columns A - D are always going to be there, we only need to start with
column E, which
' is column number 5.
For I = 5 To 52

Set objR = objWS.Cells(2, I) ' We'll always be looking at row 2 - the
column will change
If objR.Interior.ColorIndex < J Then

objWS.Columns(I).Hidden = True

End If

Next

Set objR = Nothing
Set objCell = Nothing
Set objWS = Nothing

End Sub

--
Hmm...they have the Internet on COMPUTERS now!


"Darin Kramer" wrote:


hmm...Im almost there, (but as u can tell am no programmer!), could I
have a bit more assistance.... in telling macro only to look at row 2's
background colour (i dont want to highlight all the backround colours of
the whole column....)

What i have so far is:

Public Sub DoTemplate()


Dim objWS As Excel.Worksheet
Dim objCell As Excel.Range, objR As Excel.Range
Dim I As Byte, K As Byte
Dim J As Long

Set objWS = Sheets("master")
Set objCell = Application.Selection

K = objCell.Row
J = objCell.Interior.ColorIndex

' Hide rows
'For I = 2 To 2

' If I < K Then

' objWS.Rows(I).Hidden = True

' End If

'Next

' Hide columns

For I = 2 To 52

Set objR = objWS.Cells(K, I)
If objR.Interior.ColorIndex < J Then

objWS.Columns(I).Hidden = True

End If




*** Sent via Developersdex http://www.developersdex.com ***

Many columns
 

Thanks - works perfectly!!!! Much appreciated!!!!



*** Sent via Developersdex http://www.developersdex.com ***

Many columns
 

Darin,
You can create Views of the same WS and switch between those. e.g
Hide/show initial columns.
ViewCustom ViewsAdd.. Give it a name.
Repeat for each you require.
Then have a button or some means to switch to the next view.

NickHK

"Darin Kramer" wrote in message
...


Hi Guys,

I have data within columns a to d which is static.
From Columns e onwards to say az there is a variety of information which
needs to be entered on a row by row basis. It is rather time consuming
to write macros to hide and unhide columns depedning on the view
required. Is there any easier way of group (ie viewing) columns? (eg I
want to see columns a - d, and h - k, for purposes of data entry 1, and
then to enter data set 2, I need to see A - d and m - q), and all
others hidden).

Kind Regards

D

*** Sent via Developersdex http://www.developersdex.com ***