On-Off switches (images)

[tinytoontown]

Hi

So, i'm using excel 365 and i am creating a preferences page for users to be able to show/hide columns in my [Data] tab which has 94 columns.

I have my images to show/hide column C [DataColumn_C_Hide] and [DataColumn_C_Show]
(These will be replicated and the name adjusted 94 times, unless someone can offer a neater solution)

at the moment i have these 2 subs, but i would really like to pass in the [picture name] and the [column] reference in to just on sub

Sub DataColumn_C_Show()
Application.ScreenUpdating = False
Data.Columns("C:C").ColumnWidth = 20
Preference.Shapes("DataColumn_C_Hide").Visible = False
Preference.Shapes("DataColumn_C_Show").Visible = True
Application.ScreenUpdating = True
End Sub
Sub DataColumn_C_Hide()
Application.ScreenUpdating = False
Data.Columns("C:C").ColumnWidth = 0
Preference.Shapes("DataColumn_C_Hide").Visible = True
Preference.Shapes("DataColumn_C_Show").Visible = False
Application.ScreenUpdating = True
End Sub

any help greatly welcome as i don't want to write 180 subs

 

[tinytoontown]

So i have revamped my code to this
Sub mytest()
' Call DataTabColumnHide("DataColumn_A_Show", "A:A")
Call DataTabColumnHide("DataColumn_A_Hide", "A:A")
End Sub


Sub DataTabColumnHide(ByRef image As String, ByRef column As String)
Application.ScreenUpdating = False


If Right(image, 4) = "Show" Then
'image clicked is Show
imageToShow = image
imageToHide = Replace(image, "Show", "Hide")
Else
'image clicked is Hide
imageToShow = Replace(image, "Hide", "Show")
imageToHide = image
End If

If Data.Columns(column).ColumnWidth = 0 Then
Data.Columns(column).ColumnWidth = 20
Else
Data.Columns(column).ColumnWidth = 0
End If

If Preference.Shapes(imageToHide).Visible = True Then
Preference.Shapes(imageToHide).Visible = False
Preference.Shapes(imageToShow).Visible = True
Else
Preference.Shapes(imageToHide).Visible = False
Preference.Shapes(imageToShow).Visible = True
End If


Application.ScreenUpdating = True
End Sub

all i need to do is pass in the name of my picture and a cell reference containing the column data

 

[My Aswer Is This]

I see a lot of code here but do you have any of this working?

And would you please explain to me what your attempting to do.

Sounds like you have two images you want to click on to either hide or show a column.

So some of my questions:

What is:

Data.Columns("​

C:C

").ColumnWidth = 20
Preference.Shapes("​

DataColumn_C_Hide

").Visible = False​

If your sheet is named "Data" you cannot just write your code like that.
And what is Preference.

Is this the shape name.

I mean do you really have this script working?
Or do you just assume writing your code like this will work.
I do not think this will work but maybe you know something I do not know.

 

[tinytoontown]

I see a lot of code here but do you have any of this working?

And would you please explain to me what your attempting to do.

Sounds like you have two images you want to click on to either hide or show a column.

So some of my questions:

What is:

Data.Columns("​

C:C

").ColumnWidth = 20
Preference.Shapes("​

DataColumn_C_Hide

").Visible = False​

If your sheet is named "Data" you cannot just write your code like that.
And what is Preference.

Is this the shape name.

I mean do you really have this script working?
Or do you just assume writing your code like this will work.
I do not think this will work but maybe you know something I do not know.

Yes my revised code does work.
i have the 2 tabs, i have altered the names within the vba properties
Data
Preference

on the Preferences tab i have 2 images which look like on/off switches, i use these to hide the column on the data tab
DataColumn_C_Show
DataColumn_C_Hide

what i need to figure out is how to pass the image name in to the vba funtion