Combining 2 VBA codes [Solved/Closed]

SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - Aug 7, 2010 at 01:01 AM - Latest reply: SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen
- 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......

See more 

15 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 7, 2010 at 07:34 PM
1
Thank you
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 

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1872 users this month

SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - 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 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - 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?
SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - Aug 7, 2010 at 09:26 PM
0
Thank you
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..........
SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - Aug 10, 2010 at 03:25 PM
i could use this code. but I don't want someone else who would use this file to not accidentally change or paste in the cells which I have protected. For example I don't want the cells containing formulas to be accidentally or intentionally overwritten..
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 10, 2010 at 04:33 PM
Since you dont trust users of your file with the data/file, I am hard pressed to see how you can do what you want to do.
SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - Aug 12, 2010 at 04:02 AM
Thanks Rizvisa,,, could you help me instead of making an entire column hide when anything is changed could you make it hide just when something is pasted on a cell.. This is the code you wrote for me.

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

with this code even if I change cell formatting the entire column hides..

Thanks..
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 17, 2010 at 09:43 PM
Change this line


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




to



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

    End If 
SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - Aug 17, 2010 at 10:59 PM
thanks...