Run Multiple VBA codes on the same sheet

[EBoudreau]

I've researched how to run more than one VBA code in the same sheet and I'm extremely confused as to how to do so.

I don't necessarily want to combine two or three tasks, where as I am more interested in pasting one code command, and then just paste another below it.

Is there a way to do so if I find a code I want to use with other code already in place?

All the descriptions I've read are pretty confusing to me.

 

[EBoudreau]

BAM! That got it!!! This is just a wealth of information that I am learning as I go.
Thank you so much for your continued assistance and patience. I greatly appreciate it.

 

[EBoudreau]

Here's something that just turned up....

When I protect the sheet only allowing people to select unlocked cells to be able to enter times in Cells D55:D1585, I end up with
Run time error '1004'.

The code i'm using on this sheet is:


In the Sheet Code:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rng As Range
    
    Application.EnableEvents = False
    
    Set rng = Intersect(Target, Range("E8:CQ19, E22:CQ33, E36:CQ47"))
    If Not rng Is Nothing Then Call Capitalise(rng)
        
    Set rng = Intersect(Target, Range("D55:D1585"))
    If Not rng Is Nothing Then Call ConvertToTime(rng)
        
    Application.EnableEvents = True


End Sub

And in a module code:

Sub Capitalise(rngToConvert As Range)


    Dim rng As Range
    
    For Each rng In rngToConvert
        With rng
            If Not .HasFormula Then .Value = UCase(.Value)
        End With
    Next rng


End Sub
Sub ConvertToTime(rngToConvert As Range)


    Dim rng As Range
    
    For Each rng In rngToConvert
        rng.Value = Format(rng.Value, "00\:00")
    Next rng
    
    rngToConvert.NumberFormat = "HH:MM"


End Sub

Any idea where in there I may get that error when i just enter a time (i.e. 1234) in that range somewhere while the sheet is protected?

This may be a simple fix but I am not getting it on my own.

 

[StephenCrump]

For any code making changes to locked cells in a protected sheet, you'll need:

ActiveSheet.Unprotect  'It's ActiveSheet we want in this case, given it's Worksheet_Change event code

'Make changes here

ActiveSheet.Protect 'record a macro to get the syntax for more complicated protect, e.g with passwords

You shouldn't be getting an error if, as you say, the user can only select the unlocked cells in Cells D55:D1585. I suspect you code is a little muddled, as you suggested in Post#19. The workbook you posted, for example, had Worksheet_Change event code in a code module (where it won't trigger), rather than in the relevant sheet module.

So just check that the code you've just posted is the code sitting in the sheet module for the sheet where you're limiting user access to D55:D1585?

 

[EBoudreau]

I've transferred the code to the sheet module and still that error. The code you posted in #22, does that go into the sheet module as well?
I'm not sure if I'm taking the right steps.

 

[StephenCrump]

I've transferred the code to the sheet module and still that error. The code you posted in #22, does that go into the sheet module as well?
I'm not sure if I'm taking the right steps.

So we can be clear about what code you have where, is it possible to re-post or PM me the workbook you're currently using?

And let me know which sheet is causing the problem?

 

[EBoudreau]

Here's the link for the correct version of the file.

https://drive.google.com/open?id=0B0oQEry29JNjenNKSGhaeGJRTEE

It's the 'Frac Report' sheet that is causing the problem.

Re-statement of the problem....

Everything works fine when sheet is unprotected.
But when the sheet is protected with a password and I enter anything into any cell in range D55:D1585 and press Enter, I get the error.

 

[StephenCrump]

Try changing the code in the Sheet1 (Frac report) module to

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    
    Application.EnableEvents = False
    [B][COLOR=#ff0000]ActiveSheet.Unprotect Password:="WhateverYourPasswordIs"[/COLOR][/B]
    
    Set rng = Intersect(Target, Range("E8:CQ19, E22:CQ33, E36:CQ47"))
    If Not rng Is Nothing Then Call Capitalise(rng)
        
    Set rng = Intersect(Target, Range("D55:D1585"))
    If Not rng Is Nothing Then Call ConvertToTime(rng)
        
    [COLOR=#ff0000][B]ActiveSheet.Protect Password:="WhateverYourPasswordIs"[/B][/COLOR]
    Application.EnableEvents = True

End Sub

 

[EBoudreau]

That seemed to have taken care of it! Thank you so much for the assistance. It's greatly appreciated.
Half the time I'm not even sure how to search for what I want to do online because there's so many ways to do things.

 

[dermotdu]

Hello, I am new to VBA and need assistance on how to add multiple code to a worksheet. Basically I want to hide a number of different rows based on a selection in a drop down menu.
I can do this for a selection in cell B2 and hide rows 10 to 19, see the code below.
But I want to be able to hide Rows 20 to 25 based on a selection from a drop down in cell B3, and to hide rows 27 to 29 based on a selection in a drop down in cell B4.

Any ideas on how to add multiple codes in the one worksheet to do this?


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Rows("10:19").EntireRow.Hidden = False
Select Case Target
Case "IBM CSA Web Store Contract"
Rows("10:19").EntireRow.Hidden = True
Case "IBM Paper Contract":
Case Else:
End Select
End Sub

 

[StephenCrump]

Hello, I am new to VBA and need assistance on how to add multiple code to a worksheet.

Hi dermotdu, welcome to the Forum.

You'll need to start a new thread for this. Go here: Excel Questions

If you are signed in, you should see a "+Post new thread" button towards the bottom left of the page.