Jun 06 2020 10:47 PM
hi everyone I'm really new to macros and vba but going through net I studied and found this article to make attendance of employee for which I made accordingly. the buttons are all months like apr, may, June .... I have made the same thing thrice and every time I assign in properties of buttons to not move or merge and so on but still when I run macro all buttons get merged into 1 and I cannot see other months. also if I want to redo macro and press stop/break I cannot see the full sheet It is for this reason I have made so many sheets as I go on copying else it will take me workload to do it
please advice me for eg in sheet4 (3) why the button combines into 1 and if I want to stop macro to run so that I can see the full worksheet what should I do.
please help
thanks
Jun 07 2020 02:35 AM
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.
Jun 09 2020 11:39 PM
@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
Jun 10 2020 02:56 AM - edited Jun 10 2020 03:00 AM
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 Sub
If 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.