On-Off switches (images)

tinytoontown

New Member
Joined
Apr 13, 2019
Messages
3
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

tinytoontown

New Member
Joined
Apr 13, 2019
Messages
3
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,650
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Apr 13, 2019
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,671
Messages
5,654,655
Members
418,146
Latest member
Shnn028

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