Forum Discussion

ieesh1712's avatar
ieesh1712
Copper Contributor
Jun 07, 2020

buttons go in hiding when I run macro

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

excel
Macros and VBA

3 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Jun 07, 2020

    ieesh1712 

     

    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.

     

     

     

     

    • ieesh1712's avatar
      ieesh1712
      Copper Contributor
      Jun 10, 2020

      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

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Jun 10, 2020

        ieesh1712 

         

        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.

Resources