Forum Discussion
buttons go in hiding when I run macro
If you set the property of each shape as "Don't move or size with cells", when you hide the columns, there is no place left for the buttons since the columns they were sitting in got hidden so they gets screwed up and sit on each other.
You could set the property as Move and size with cells so that they remain intact with the source columns.
Instead of populating the attendance for each month across the columns, you may populated them across the rows as shown in the Attendance(2) Sheet (green tab).
I have created some named ranges for each month and set the same captions for all the months buttons and then assigned the following macro to all the month buttons as you need only one macro which would work for all the buttons.
The macro which should assigned to all the buttons is as below...
Sub NavigateToMonthlyAttendance()
Dim shp As Shape
Dim rng As Range
Dim strRng As String
Set shp = ActiveSheet.Shapes(Application.Caller)
strRng = shp.TextFrame2.TextRange.Text
Set rng = Range(strRng)
Application.Goto rng, True
rng.Cells(3, 3).Select
End Sub
For more details, please refer to the Sheet Attendance(2) in the attached.
Subodh_Tiwari_sktneer thanks a million solved the purpose and will just try to understand it
1 more thing if I want to view the full sheet without running vba how do I do that because even if I press stop(break) it only shows what I had pressed last time the button
If you are talking about the layout in Attendance(2) sheet, all you need is to click the Apr button and the top month April will be displayed on the top.
In this approach, no rows or columns are being hidden but the desired month range comes on the top, that's it.
As far as your original approach is concerned you may insert a button with the following code underneath it to unhide all the columns...
Sub UnHideColumns() ActiveSheet.UsedRange.Columns.Hidden = False End SubIf that takes care of your original question, please take a minute to accept the post with the solution provided as a Best Response to mark your question as Solved.