Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default passing spreadsheet cell data to macro

Also, here is my answer to your explanation in the original thread:

Assume you have a sheet (named Data) with 37 cells in column B containing an
X if you want a column hidden. If I want column B hidden, I put an X in B2,
if I want column AA hidden, I put an X in B27.

You can have the 37 labels in column A, but that is not needed by the macro.
Assume this sheet is named "data" and the only other sheets in the workbook
are the 7 sheets.

Sub HandleColumns()
Dim varr()
Dim sStr As String, rng As Range
Dim sh As Worksheet
On Error Resume Next
With Worksheets("Data")
Set rng = .Range(.Cells(1, 2), .Cells(37, 2)). _
SpecialCells(xlConstants, xlTextValues)
End With
On Error GoTo 0
if rng is nothing then exit sub
ReDim varr(1 To rng.Count)
sStr = ""
For Each cell In rng
sCol = Left(Cells(1, cell.Row).Address(0, 0), 2 + (cell.Row < 27))
sStr = sStr & sCol & ":" & sCol & ","
Next
sStr = Left(sStr, Len(sStr) - 1)
If Len(Trim(sStr)) = 0 Then Exit Sub
For Each sh In Worksheets
If LCase(sh.Name) < "data" Then
sh.Columns.Hidden = False
sh.Range(sStr).EntireColumn.Hidden = True
End If
Next

End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Dim sh as Worksheet, sCol as String
sCol = Trim(Worksheets("Sheet1").Range("N42").Value)
for each sh in ActiveWindow.SelectedSheets
sh.Columns(sCol & ":" & sCol).Hidden = True
Next

--
Regards,
Tom Ogilvy


"Neal" wrote in message
...
I have a macro to hide a column in an array of worksheets. I would like

to have
it get the column from a cell in a worksheet. How do I go about doing

that? In
other words, If cell N42 has a J in it, then the J would be transferred

to
the
macro.

Dim sh As Worksheet
For Each sh In ActiveWindow.SelectedSheets
sh.Columns("g:g").Hidden = True
Next


Thanks

Neal






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing data to fix cell Amnon Wilensky Excel Worksheet Functions 4 March 10th 09 03:58 PM
Passing variables between a form and macro David New Users to Excel 1 October 5th 05 04:42 AM
Passing variables between a form and macro David New Users to Excel 4 September 23rd 05 11:57 AM
passing arguments from an excel macro to a word macro KWE39 Excel Discussion (Misc queries) 1 July 7th 05 03:56 PM
Passing Text from User Form to Spreadsheet Betsy[_2_] Excel Programming 1 August 6th 03 02:33 AM


All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"