Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Hidding Script not working
Afternoon Guys,
I'm trying to run a script that will hide selected columns. These columns are selected via spreadsheet in excel. When you click on a particular cell, it displays a column letter, eg, click "A3" as Yes, -- select column "D:D" (results located in C1), select team 'Show Some' (located in G2). Once these have been selected, the program will then select sheet 'Scorecard' and hide column D:D. Please see below. It works fine when it isn't selecting a sheet but as soon as a sheet select is placed in the script...i hides everything in that sheet. it should hide selected columns not everything. Please help if you can. Cheers. Sub TestingColumn2() Dim Columns, Hide As String Hide = Range("H3") If Range("G2") = Range("G2") Then Sheets("Administration").Select Columns = Range("C1") Sheets("Scorecard").Select Range("D:AS").Select Range("A45") = "" Selection.EntireColumn.Hidden = False Range(Columns).Activate Selection.EntireColumn.Hidden = Hide Range("A45").Value = "Working" Else Range("A46") = "Not Working" End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Hidding Script not working
On May 10, 11:40 pm, chinny wrote:
Afternoon Guys, I'm trying to run a script that will hide selected columns. These columns are selected via spreadsheet in excel. When you click on a particular cell, it displays a column letter, eg, click "A3" as Yes, -- select column "D:D" (results located in C1), select team 'Show Some' (located in G2). Once these have been selected, the program will then select sheet 'Scorecard' and hide column D:D. Please see below. It works fine when it isn't selecting a sheet but as soon as a sheet select is placed in the script...i hides everything in that sheet. it should hide selected columns not everything. Please help if you can. Cheers. Sub TestingColumn2() Dim Columns, Hide As String Hide = Range("H3") If Range("G2") = Range("G2") Then Sheets("Administration").Select Columns = Range("C1") Sheets("Scorecard").Select Range("D:AS").Select Range("A45") = "" Selection.EntireColumn.Hidden = False Range(Columns).Activate Selection.EntireColumn.Hidden = Hide Range("A45").Value = "Working" Else Range("A46") = "Not Working" End If End Sub Without seeing your sheet, it's difficult to see where you're going. I've made some assumptions, so here's a possible solution. First, you don't need all the selects unless you just want them. You can do most of this without actually selecting anything. Sub TestingColumn2() Dim Columns, Hide As String Hide = Range("H3") 'assumed that this value was either TRUE 'or FALSE based on some criteria (checkbox, radio, etc...) If Range("G2") = Range("G2") Then 'I don't really understand why you're testing if a cell equals itself... 'As it always will be true, why have the if statement? 'Are the ranges supposed to be on different sheets? If so, you need to state that. Columns = Sheets("Administration").Range("C1").Value ' assumed that you meant the actual string value for variable 'Columns was the value in the cell C1 on the Admin sheet. Sheets("Scorecard").Range("A45") = "" Sheets("Scorecard").Range("D:AS").EntireColumn.Hid den = False 'Did you mean to hide 42 columns here? Range(Columns).EntireColumn.Hidden = Hide Range("A45").Value = "Working" Else Range("A46") = "Not Working" End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Hidding Script not working
Mornig okrob,
Unfortunately it isn't exactly what i wanted, For example; in cell G2 (administration sheet), there is a team select. depending on the team selected in G2, it will hide certain columns in 'scorecard' sheet. eg. if cell G2 is 'DC Team' then select columns B:B,E:E,G:K and hide those columns. each team has different criteria in hiding selected columns in the scorecard sheet. i've use a script that has all the criteria but it was too long, that's why i made this script, it was working at one stage, but not anymore. if you can, please help "okrob" wrote: On May 10, 11:40 pm, chinny wrote: Afternoon Guys, I'm trying to run a script that will hide selected columns. These columns are selected via spreadsheet in excel. When you click on a particular cell, it displays a column letter, eg, click "A3" as Yes, -- select column "D:D" (results located in C1), select team 'Show Some' (located in G2). Once these have been selected, the program will then select sheet 'Scorecard' and hide column D:D. Please see below. It works fine when it isn't selecting a sheet but as soon as a sheet select is placed in the script...i hides everything in that sheet. it should hide selected columns not everything. Please help if you can. Cheers. Sub TestingColumn2() Dim Columns, Hide As String Hide = Range("H3") If Range("G2") = Range("G2") Then Sheets("Administration").Select Columns = Range("C1") Sheets("Scorecard").Select Range("D:AS").Select Range("A45") = "" Selection.EntireColumn.Hidden = False Range(Columns).Activate Selection.EntireColumn.Hidden = Hide Range("A45").Value = "Working" Else Range("A46") = "Not Working" End If End Sub Without seeing your sheet, it's difficult to see where you're going. I've made some assumptions, so here's a possible solution. First, you don't need all the selects unless you just want them. You can do most of this without actually selecting anything. Sub TestingColumn2() Dim Columns, Hide As String Hide = Range("H3") 'assumed that this value was either TRUE 'or FALSE based on some criteria (checkbox, radio, etc...) If Range("G2") = Range("G2") Then 'I don't really understand why you're testing if a cell equals itself... 'As it always will be true, why have the if statement? 'Are the ranges supposed to be on different sheets? If so, you need to state that. Columns = Sheets("Administration").Range("C1").Value ' assumed that you meant the actual string value for variable 'Columns was the value in the cell C1 on the Admin sheet. Sheets("Scorecard").Range("A45") = "" Sheets("Scorecard").Range("D:AS").EntireColumn.Hid den = False 'Did you mean to hide 42 columns here? Range(Columns).EntireColumn.Hidden = Hide Range("A45").Value = "Working" Else Range("A46") = "Not Working" End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Script Not working | Excel Worksheet Functions | |||
Sort Column Script | New Users to Excel | |||
Sort Column Script | Excel Programming | |||
Hidding columns not working | Excel Programming | |||
Excel VB Script Not Working in Explorer | Excel Programming |