PDA

View Full Version : [SOLVED] I can not activate these two macros together



k0st4din
10-22-2013, 09:38 PM
Hello
can anyone tell me why I can not activate these two macros together?

Sub macros2222223()

HideUnhideColumns
HideUnhideRows
End Sub 'not working

Sub HideUnhideColumns()
Dim myBTN As Button, cll As Range

With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
If Trim(UCase(myBTN.Caption)) = "HIDE" Then

For Each cll In .Range("Sum").Cells
cll.EntireColumn.Hidden = cll.Value = 0
Next cll

myBTN.Caption = "Show"

Else
.Range("Sum").EntireColumn.Hidden = False

myBTN.Caption = "Hide"
End If
On Error Resume Next
.UsedRange.Cells.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit
On Error GoTo 0
End With
End Sub




Sub HideUnhideRows()
Dim myBTN As Button, cll As Range

With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
If Trim(UCase(myBTN.Caption)) = "HIDE" Then

For Each cll In .Range("Num").Cells
cll.EntireRow.Hidden = cll.Value = ""
Next cll

myBTN.Caption = "Show"

Else
.Range("Num").EntireRow.Hidden = False

myBTN.Caption = "Hide"
End If
On Error Resume Next
.UsedRange.Cells.SpecialCells(xlCellTypeVisible).EntireRow.AutoFit
On Error GoTo 0
End With
End Sub



Sub CallprobMacs()

Call HideprobUnhideColumns
Call HideprobUnhideRows
End Sub 'not working



Sub runall1()

Application.Run "HideUnhideColumns"
Application.Run "HideUnhideRows"
End Sub 'not working

p45cal
10-22-2013, 11:58 PM
Both macros change the button caption, on which what each macro does depends, so after the first macro has run the caption has changed, when the second macro runs it re-examines the caption to decide what it should do.

I haven't investigated, but I'm not even sure what Application.caller returns now that you're calling these macros from another macro.

In my answer to another recent thread of yours (http://www.vbaexpress.com/forum/showthread.php?47984-Macro-show-hide-with-show-hide-zeros-in-cells), I included some commented-out code which does this.

k0st4din
10-23-2013, 12:17 AM
Hello
Yes, it is done after macro in my previous post, I changed things up a bit so I can hide and rows in which both macro individually work great.
Ie I to understand that I just need to change (say with different letters) each macro and then merge them?
or
to enable:
'Application.Stsreenupdating = False - to stop screen flicker
I understand that you have to be that way:

Sub HideUnhide() Dim myBTN As Button, cll As Range

With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
If Trim(UCase(myBTN.Caption)) = "HIDE" Then 'the caption on the button is what decides whether to show or hide.
Application.ScreenUpdating = False 'optional to stop screen flicker.
For Each cll In .Range("Sum").Cells
cll.EntireColumn.Hidden = cll.Value = 0
Next cll
' For Each cll In .Range("Sum2").Cells 'un-comment these three lines to hide zero rows too.
' cll.EntireRow.Hidden = cll.Value = 0
' Next cll
myBTN.Caption = "Show"
Application.ScreenUpdating = True 'REQUIRED!!!! if you've enabled the line Application.ScreenUpdating = False above.
Else
.Range("Sum").EntireColumn.Hidden = False
'.Range("Sum2").EntireRow.Hidden = False'un-comment if you've un-commented the row-hiding lines above (it shows the rows).
myBTN.Caption = "Hide"
End If
On Error Resume Next
.UsedRange.Cells.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit
On Error GoTo 0
End With
End Sub
Or if not can you help me to be right and not wrong?
Thank you in advance for your cooperation on your part.

p45cal
10-23-2013, 12:33 AM
I only partly understand that last post; perhaps you should enable all the other commented-out lines of code?

k0st4din
10-23-2013, 01:10 AM
In your words must activate (allow) all the other stuff, but it does not happen things and gives me an error.
I want to tell you that I'm wrong somewhere, but I do not know where.
And so I ask you:
The first macro is you -> works as hidden columns containing zeros.
The second macro (reworked it) to let me hide rows containing zeros.
Each macro itself works.
You're telling me that you have to release all written with an asterisk (') for it to work and so that I can combine them into a single button. But it is not
The first is for the columns and i removed all (') look at it.
The second is revised to rows - there are also removed all (')
Or something wrong?

Sub HidemimiUnhide()Dim myBTN As Button, cll As Range
With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
If Trim(UCase(myBTN.Caption)) = "HIDE" Then 'the caption on the button is what decides whether to show or hide.
Application.ScreenUpdating = False 'optional to stop screen flicker.
For Each cll In .Range("Sum").Cells
cll.EntireColumn.Hidden = cll.Value = 0
Next cll
For Each cll In .Range("Sum2").Cells 'un-comment these three lines to hide zero rows too.
cll.EntireRow.Hidden = cll.Value = 0
Next cll
myBTN.Caption = "Show"
Application.ScreenUpdating = True 'REQUIRED!!!! if you've enabled the line Application.ScreenUpdating = False above.
Else
.Range("Sum").EntireColumn.Hidden = False
.Range("Sum2").EntireRow.Hidden = False 'un-comment if you've un-commented the row-hiding lines above (it shows the rows).
myBTN.Caption = "Hide"
End If
On Error Resume Next
.UsedRange.Cells.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit
On Error GoTo 0
End With
End Sub


Sub HidemimitoUnhideRows()
Dim myBTN As Button, cll As Range

With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
If Trim(UCase(myBTN.Caption)) = "HIDE" Then
Application.ScreenUpdating = False
For Each cll In .Range("Num").Cells
cll.EntireRow.Hidden = cll.Value = 0
Next cll
For Each cll In .Range("Num2").Cells 'un-comment these three lines to hide zero rows too.
cll.EntireRow.Hidden = cll.Value = 0
Next cll
myBTN.Caption = "Show"
Application.ScreenUpdating = True
Else
.Range("Num").EntireRow.Hidden = False
.Range("Num2").EntireRow.Hidden = False
myBTN.Caption = "Hide"
End If
On Error Resume Next
.UsedRange.Cells.SpecialCells(xlCellTypeVisible).EntireRow.AutoFit
On Error GoTo 0
End With
End Sub



I tried in this way, but it gives me an error on this line:
:. Range ("Num2"). EntireRow.Hidden = False 'un-comment i ......................:


Sub mimHidemimiUnhide()Dim myBTN As Button, cll As Range
With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
If Trim(UCase(myBTN.Caption)) = "HIDE" Then 'the caption on the button is what decides whether to show or hide.
Application.ScreenUpdating = False 'optional to stop screen flicker.
For Each cll In .Range("Sum").Cells
cll.EntireColumn.Hidden = cll.Value = 0
Next cll
For Each cll In .Range("Num2").Cells 'un-comment these three lines to hide zero rows too.
cll.EntireRow.Hidden = cll.Value = 0
Next cll
myBTN.Caption = "Show"
Application.ScreenUpdating = True 'REQUIRED!!!! if you've enabled the line Application.ScreenUpdating = False above.
Else
.Range("Sum").EntireColumn.Hidden = False
.Range("Num2").EntireRow.Hidden = False 'un-comment if you've un-commented the row-hiding lines above (it shows the rows).
myBTN.Caption = "Hide"
End If
On Error Resume Next
.UsedRange.Cells.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit
.UsedRange.Cells.SpecialCells(xlCellTypeVisible).EntireRow.AutoFit
On Error GoTo 0
End With
End Sub

p45cal
10-23-2013, 02:13 AM
Looking at your file, you didn't set up a second named range (Sum2) as you did for the first macro (Sum). See attached.

k0st4din
10-23-2013, 03:04 AM
Oh my God I saw where my error.
Received is because I've added one more, but should not:
('. UsedRange.Cells.SpecialCells (xlCellTypeVisible). EntireRow.AutoFit)
Many thanks for your help.
I knew I made ​​a mistake somewhere, but thanks to you, everything is fine.
Bow before you learned.