Combining 2 VBA codes

Solved/Closed

Excel

SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010 - Aug 7, 2010 at 01:01 AM
SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010 - Aug 17, 2010 at 10:59 PM

Hi.. I need to combine 2 codes together. I'm very new to VBA. These are the 2 codes I need to combine. when I paste the 2nd code it doesnt work properly

Code1
Private Sub Worksheet_Change(ByVal Target As Range)
'Erik Van Geit
'only allow PasteValues

If Application.CutCopyMode = False Then Exit Sub

With Application
.ScreenUpdating = False
.EnableEvents = False
.Undo
Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

Code2
Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A2") = >1 Then
Columns("A").EntireColumn.Hidden = True
End If

End Sub

any help pls......

How to combine 2 vba code in excel
Tentacle locker 2 - Download - Adult games
Five nights in anime 2 - Download - Adult games
Battery reset code - Guide
How to get whatsapp verification code online - Guide
Number to words in excel formula without vba - Guide

2 responses

Answer 1 / 2

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 7, 2010 at 07:34 PM

There is lot of ambiguity on what is your intentions

This is presuming that irrespective of fact if you paste the value or not you want to hide the column

Private Sub Worksheet_Change(ByVal Target As Range) 
'Erik Van Geit 
'only allow PasteValues 

    If Application.CutCopyMode <> False Then 
     
        With Application 
            .ScreenUpdating = False 
            .EnableEvents = False 
            .Undo              
            
            Target.PasteSpecial _
                        Paste:=xlPasteValues, _
                        Operation:=xlNone, _
                        SkipBlanks:=False, _
                        Transpose:=False
             
            .EnableEvents = True 
            .ScreenUpdating = True 

        End With 
     
    End If 
     
    If Range("A2") >= 1 Then Columns("A").EntireColumn.Hidden = True 
     
End Sub

SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010
Aug 7, 2010 at 09:20 PM

Thanks rizvi...... your my hero.............. This is exactly what I wanted. I'm trying to develop some sort of stock updating system this is just 1 phase in it. would you be able to help me out with a few more things..... Thanks again for the code

SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010
Aug 7, 2010 at 09:24 PM

forgot to tell you what I wanted..... is there a way to hide any column if a value is pasted on a cell?

Answer 2 / 2

SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010
Aug 7, 2010 at 09:26 PM

Thanks.... this is exactly what I wanted... is there a way to hide any column in the worksheet if a value is only pasted.... thanks..........

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 9, 2010 at 10:51 AM

Sorry I did not get you. You mean to say that if you paste some thing in A4, you want A column to be hidden. But if you paste on Cell F9 then you want F column to be hidden ?

SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010
Aug 9, 2010 at 11:36 AM

Yes. That is what I want. If I paste on any cell in the worksheet I want that entire column to be hidden

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 9, 2010 at 02:01 PM

SARC, you can use this

Instead of this line
If Range("A2") >= 1 Then Columns("A").EntireColumn.Hidden = True
The above line is saying if cell A2 >=1 then hide column A

The line below is saying that loop true each cell that was CHANGED (not pasted, mind you) . If the column of that cell is visible, then hide it

    For Each Cell In Target 
        If (Not (Cell.EntireColumn.Hidden)) Then 
            Cell.EntireColumn.Hidden = True 
        End If 
    Next

SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010
Aug 9, 2010 at 09:42 PM

Thanks, this works too. But then il have to insert every thing I need before I write the code. And if I need to change something in the future manually I will have to stop the code again. thanks for this Rizvisa, it would be great if you could make the columns hide if something was pasted.....

SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010
Aug 10, 2010 at 05:07 AM

A new problem.... it worked perfectly till I protected the sheet. I get this error when I protect the sheet and paste values.

"Unable to set the Hidden Property of the Range class"

what should I do??

Similar discussions

Excel make cell flash red

Auto generate serial number in excel based on Column

Select and copy only nonblank cells

Excel VBA - Change cell color based on value

Execute Macro On Calculated Cell change

Creating search and update command buttons in a userform

Similar discussions

Subscribe To Our Newsletter!