VBA code to have user select different column as X axis

[rathalex]

Hello,

I am having trouble creating a code that would allow a user to pick which column(based on names in cells A1-D1) to use as x axis to construct scatter xy plot. The y data is in column E and remains constant. So i need an input box with 4 options probably and possible IF statement so that user could select which column to plot vs column E. Any suggestions would be greatly appreciated since Im ver new to VBA. Thank you

 

[Giordano Bruno]

One fairly easy way to achieve this is to make the range A:D your x axis, in effect making four different charts. Since charts will not take date from hidden cells, your code need only hide columns A:D and then unhide the column you wish to use.

 

[rathalex]

How do I make an IF statement or code to prompt user to choose which column to use though? Also by the name in the 1st row not actual column A-D names

 

[Giordano Bruno]

You will have to use code to hide and unhide columns. There are various options, but the easiest way to describe a solution is to name a cell in each data column, say Data1, Data2, etc. Then insert a button at the top of each column, attach code similar to the following code to each button.

Sub ShowData1()
    Call HideAllData
    Range("Data1").EntireColumn.Hidden = False
End Sub

You will also need the code to hide all data columns

Sub HideAllData()
    Range("Data1").EntireColumn.Hidden = True
    Range("Data2").EntireColumn.Hidden = True
    Range("Data3").EntireColumn.Hidden = True
    Range("Data4").EntireColumn.Hidden = True
End Sub

 

[rathalex]

There is no options without hiding the column? Like create an inputbox and based on user's selection,excel would pick a range?

 

[Giordano Bruno]

That's only slightly more complex. The best solution would be for you to record a macro while you edit the chart to change the X axis range and then change the line which looks like "ActiveChart.FullSeriesCollection(1).Values = "=Sheet1!$F$4:$F$15"" to a reference that suits your various options. Then use a case structure to select the code based upon the choice made in the input box. Or you could create a Userform with some option buttons to let the user select the X axis.