Formatting Macro

B

BBrandt

Board Regular
Joined
Jul 14, 2008
Messages
155
I'm working on creating a table that dynamically adjusts its appearance based on what data the user inputs. Eventually, this table will be used to feed an existing spreadsheet with data to generate an entire new spreadsheet worth of results.

The way it works now is that there are several specific cells that can trigger the table to reformat itself, accomplished by temporarily cutting the table to a hidden part of the sheet and then restoring the relevant cells depending on what a user selects from a drop-down list. The trouble is that everytime I try to run the macro for the "typb_1" cell input, excel freezes up and I can't do anything until I press escape to break the code. Also, whenever "numtyp_1" = 2, I did a little test and parts of my code that shouldn't be accessed seem to not only run, but run several times before the code stops executing.

Can anyone please take a look at what I've done and give me some advice on how to clean it up and make it a little more efficient? Alternately, could someone please point out what part of my code is causing excel to lock up? Thanks in advance, I'm flying a little blind without your help.

Worksheet_Change code:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
'Variables
Dim Trigger1a As Range
Set Trigger1a = [typa_1]
Dim Trigger1b As Range
Set Trigger1b = [numtyp_1]
Dim Trigger1c As Range
Set Trigger1c = [typb_1]
Dim Trigger1d As Range
Set Trigger1d = [stack_1]
 
'If cells other than "typa_1" cell are changed, do nothing,
'otherwise run the appropriate macro
If Not Application.Intersect(Trigger1a, Range(Target.Address)) _
Is Nothing Then FormatReinforcement1A
'If cells other than "numtyp_1" are changed, do nothing, otherwise
'run macros based on whether numtyp is specified as 1 or 2
If Not Application.Intersect(Trigger1b, Range(Target.Address)) _
Is Nothing Then
If [numtyp_1] = 1 Then FormatReinforcement1Num1
If [numtyp_1] = 2 Then FormatReinforcement1Num2
End If
'If cells other than "typb_1" are changed, do nothing,,
'otherwise run the appropriate macro
If Not Application.Intersect(Trigger1c, Range(Target.Address)) _
Is Nothing Then FormatReinforcement1B
Application.ScreenUpdating = Old_ScrUpdate
End Sub

And then the following code is all in a Module, with the goal that it get called depending on the circumstances in the worksheet_change event:

Code: 
Sub FormatReinforcement1A()
'----------------------------------------------------
'Actions based on the type of the first reinforcement
'----------------------------------------------------
'Variables
Dim Channel As Range
Set Channel = [Tlcha_1:Brcha_1]
Dim Plate As Range
Set Plate = [Tlpa_1:Brpa_1]
Dim Round As Range
Set Round = [Tlsra_1:Brsra_1]
Dim TopPart As Range
Set TopPart = [Tlsr_1:stackright_1]
Dim Offset As Range
Set Offset = [leftoff_1:rightoff_1]
Dim ChannelB As Range
Set ChannelB = [Tlchb_1:Brchb_1]
Dim PlateB As Range
Set PlateB = [Tlpb_1:Brpb_1]
Dim RoundB As Range
Set RoundB = [Tlsrb_1:Brsrb_1]
Dim CompCheck As Range
Set CompCheck = [Tlcc_1:Brcc_1]
 
'Storage
Channel.Cut ([G69:I70])
Plate.Cut ([G71:I73])
Round.Cut ([G74:I75])
TopPart.Cut ([G76:I79])
Offset.Cut ([G80:I80])
ChannelB.Cut ([G81:I82])
PlateB.Cut ([G83:I85])
RoundB.Cut ([G86:I87])
CompCheck.Cut ([G89:I94])
If [typa_1] = "Channel" Then
    'Recall relevant input cells
    Channel.Cut ([G19:I20])
    'Check number of reinforcements, if 1, move compression
    'check table into correct place
    If [numtyp_1] = 1 Then CompCheck.Cut ([G22:I27])
 
    If [numtyp_1] = 2 Then
        If [typb_1] = "Channel" Then
            If [stack_1] = "Yes" Then
                TopPart.Cut ([G21:I24])
                ChannelB.Cut ([G25:I26])
                CompCheck.Cut ([G28:I33])
            End If
            If [stack_1] = "No" Then
                TopPart.Cut ([G21:I24])
                Offset.Cut ([G25:I25])
                ChannelB.Cut ([G26:I27])
                CompCheck.Cut ([G29:I34])
            End If
        End If
        If [typb_1] = "Plate" Then
            If [stack_1] = "Yes" Then
                TopPart.Cut ([G21:I24])
                PlateB.Cut ([G25:I27])
                CompCheck.Cut ([G29:I34])
            End If
            If [stack_1] = "No" Then
                TopPart.Cut ([G21:I24])
                Offset.Cut ([G25:I25])
                PlateB.Cut ([G26:I28])
                CompCheck.Cut ([G30:I35])
            End If
        End If
        If [typb_1] = "Solid Round" Then
            If [stack_1] = "Yes" Then
                TopPart.Cut ([G21:I24])
                RoundB.Cut ([G25:I26])
                CompCheck.Cut ([G28:I33])
            End If
            If [stack_1] = "No" Then
                TopPart.Cut ([G21:I24])
                Offset.Cut ([G25:I25])
                RoundB.Cut ([G26:I27])
                CompCheck.Cut ([G29:I34])
            End If
        End If
    End If
End If
 
If [typa_1] = "Plate" Then
    'Recall relevant input cells
    Plate.Cut ([G19:I21])
    'Check number of reinforcements, if 1, move compression
    'check table into correct place
    If [numtyp_1] = 1 Then CompCheck.Cut ([G23:I28])
 
    If [numtyp_1] = 2 Then
        If [typa_1] = "Plate" Then
            If [typb_1] = "Channel" Then
                If [stack_1] = "Yes" Then
                    TopPart.Cut ([G22:I25])
                    ChannelB.Cut ([G26:I27])
                    CompCheck.Cut ([G29:I34])
                End If
                If [stack_1] = "No" Then
                    TopPart.Cut ([G22:I25])
                    Offset.Cut ([G26:I26])
                    ChannelB.Cut ([G27:I28])
                    CompCheck.Cut ([G30:I35])
                End If
            End If
            If [typb_1] = "Plate" Then
                If [stack_1] = "Yes" Then
                    TopPart.Cut ([G22:I25])
                    PlateB.Cut ([G26:I28])
                    CompCheck.Cut ([G30:I35])
                End If
                If [stack_1] = "No" Then
                    TopPart.Cut ([G22:I25])
                    Offset.Cut ([G26:I26])
                    PlateB.Cut ([G27:I29])
                    CompCheck.Cut ([G31:I36])
                End If
            End If
            If [typb_1] = "Solid Round" Then
                If [stack_1] = "Yes" Then
                    TopPart.Cut ([G22:I25])
                    RoundB.Cut ([G26:I27])
                    CompCheck.Cut ([G29:I34])
                End If
                If [stack_1] = "No" Then
                    TopPart.Cut ([G22:I25])
                    Offset.Cut ([G26:I26])
                    RoundB.Cut ([G27:I28])
                    CompCheck.Cut ([G30:I35])
                End If
            End If
        End If
    End If
End If
If [typa_1] = "Solid Round" Then
    'Recall relevant input cells
    Round.Cut ([G19:I20])
    'Check number of reinforcements, if 1, move compression
    'check table into correct place
    If [numtyp_1] = 1 Then CompCheck.Cut ([G22:I27])
 
    If [numtyp_1] = 2 Then
        If [typb_1] = "Channel" Then
            If [stack_1] = "Yes" Then
                TopPart.Cut ([G21:I24])
                ChannelB.Cut ([G25:I26])
                CompCheck.Cut ([G28:I33])
            End If
            If [stack_1] = "No" Then
                TopPart.Cut ([G21:I24])
                Offset.Cut ([G25:I25])
                ChannelB.Cut ([G26:I27])
                CompCheck.Cut ([G29:I34])
            End If
        End If
        If [typb_1] = "Plate" Then
            If [stack_1] = "Yes" Then
                TopPart.Cut ([G21:I24])
                PlateB.Cut ([G25:I27])
                CompCheck.Cut ([G29:I34])
            End If
            If [stack_1] = "No" Then
                TopPart.Cut ([G21:I24])
                Offset.Cut ([G25:I25])
                PlateB.Cut ([G26:I28])
                CompCheck.Cut ([G30:I35])
            End If
        End If
        If [typb_1] = "Solid Round" Then
            If [stack_1] = "Yes" Then
                TopPart.Cut ([G21:I24])
                RoundB.Cut ([G25:I26])
                CompCheck.Cut ([G28:I33])
            End If
            If [stack_1] = "No" Then
                TopPart.Cut ([G21:I24])
                Offset.Cut ([G25:I25])
                    RoundB.Cut ([G26:I27])
                CompCheck.Cut ([G29:I34])
            End If
        End If
    End If
End If
 
End Sub
Sub FormatReinforcement1B()
'-----------------------------------------------------
'Actions based on the type of the second reinforcement
'-----------------------------------------------------
'Variables
Dim TopPart As Range
Set TopPart = [Tlsr_1:stackright_1]
Dim Offset As Range
Set Offset = [leftoff_1:rightoff_1]
Dim ChannelB As Range
Set ChannelB = [Tlchb_1:Brchb_1]
Dim PlateB As Range
Set PlateB = [Tlpb_1:Brpb_1]
Dim RoundB As Range
Set RoundB = [Tlsrb_1:Brsrb_1]
Dim CompCheck As Range
Set CompCheck = [Tlcc_1:Brcc_1]
 
'
'
'
'Any code I put here causes excel to lock up.
'
'
'
 
End Sub
Sub FormatReinforcement1Num1()
'--------------------------------------------------
'Actions for when the number of reinforcements is 1
'--------------------------------------------------
'Variables
Dim Channel As Range
Set Channel = [Tlcha_1:Brcha_1]
Dim Plate As Range
Set Plate = [Tlpa_1:Brpa_1]
Dim Round As Range
Set Round = [Tlsra_1:Brsra_1]
Dim TopPart As Range
Set TopPart = [Tlsr_1:stackright_1]
Dim Offset As Range
Set Offset = [leftoff_1:rightoff_1]
Dim ChannelB As Range
Set ChannelB = [Tlchb_1:Brchb_1]
Dim PlateB As Range
Set PlateB = [Tlpb_1:Brpb_1]
Dim RoundB As Range
Set RoundB = [Tlsrb_1:Brsrb_1]
Dim CompCheck As Range
Set CompCheck = [Tlcc_1:Brcc_1]
'Store
TopPart.Cut ([G76:I79])
Offset.Cut ([G80:I80])
ChannelB.Cut ([G81:I82])
PlateB.Cut ([G83:I85])
RoundB.Cut ([G86:I87])
CompCheck.Cut ([G89:I94])
Channel.Cut ([G69:I70])
Plate.Cut ([G71:I73])
Round.Cut ([G74:I75])
    'Move the compression check table as necessary
    If [typa_1] = "Channel" Then
        CompCheck.Cut ([G22:I27])
        Channel.Cut ([G19:I20])
    End If
 
    If [typa_1] = "Plate" Then
        CompCheck.Cut ([G23:I28])
        Plate.Cut ([G19:I21])
    End If
 
    If [typa_1] = "Solid Round" Then
        CompCheck.Cut ([G22:I27])
        Round.Cut ([G19:I20])
    End If
End Sub
Sub FormatReinforcement1Num2()
'--------------------------------------------------
'Actions for when the number of reinforcements is 2
'--------------------------------------------------
'Variables
Dim TopPart As Range
Set TopPart = [Tlsr_1:stackright_1]
Dim Offset As Range
Set Offset = [leftoff_1:rightoff_1]
Dim ChannelB As Range
Set ChannelB = [Tlchb_1:Brchb_1]
Dim PlateB As Range
Set PlateB = [Tlpb_1:Brpb_1]
Dim RoundB As Range
Set RoundB = [Tlsrb_1:Brsrb_1]
Dim CompCheck As Range
Set CompCheck = [Tlcc_1:Brcc_1]
 
'Storage
TopPart.Cut ([G76:I79])
Offset.Cut ([G80:I80])
ChannelB.Cut ([G81:I82])
PlateB.Cut ([G83:I85])
RoundB.Cut ([G86:I87])
CompCheck.Cut ([G89:I94])
If [typa_1] = "Channel" Then
    If [typb_1] = "Channel" Then
        If [stack_1] = "Yes" Then
            TopPart.Cut ([G21:I24])
            ChannelB.Cut ([G25:I26])
            CompCheck.Cut ([G28:I33])
        End If
        If [stack_1] = "No" Then
            TopPart.Cut ([G21:I24])
            Offset.Cut ([G25:I25])
            ChannelB.Cut ([G26:I27])
            CompCheck.Cut ([G29:I34])
        End If
    End If
    If [typb_1] = "Plate" Then
        If [stack_1] = "Yes" Then
            TopPart.Cut ([G21:I24])
            PlateB.Cut ([G25:I27])
            CompCheck.Cut ([G29:I34])
        End If
        If [stack_1] = "No" Then
            TopPart.Cut ([G21:I24])
            Offset.Cut ([G25:I25])
            PlateB.Cut ([G26:I28])
            CompCheck.Cut ([G30:I35])
        End If
    End If
    If [typb_1] = "Solid Round" Then
        If [stack_1] = "Yes" Then
            TopPart.Cut ([G21:I24])
            RoundB.Cut ([G25:I26])
            CompCheck.Cut ([G28:I33])
        End If
        If [stack_1] = "No" Then
            TopPart.Cut ([G21:I24])
            Offset.Cut ([G25:I25])
            RoundB.Cut ([G26:I27])
            CompCheck.Cut ([G29:I34])
        End If
    End If
End If
 
If [typa_1] = "Plate" Then
    If [typb_1] = "Channel" Then
        If [stack_1] = "Yes" Then
            TopPart.Cut ([G22:I25])
            ChannelB.Cut ([G26:I27])
            CompCheck.Cut ([G29:I34])
        End If
        If [stack_1] = "No" Then
            TopPart.Cut ([G22:I25])
            Offset.Cut ([G26:I26])
            ChannelB.Cut ([G27:I28])
            CompCheck.Cut ([G30:I35])
        End If
    End If
    If [typb_1] = "Plate" Then
        If [stack_1] = "Yes" Then
            TopPart.Cut ([G22:I25])
            PlateB.Cut ([G26:I28])
            CompCheck.Cut ([G30:I35])
        End If
        If [stack_1] = "No" Then
            TopPart.Cut ([G22:I25])
            Offset.Cut ([G26:I26])
            PlateB.Cut ([G27:I29])
            CompCheck.Cut ([G31:I36])
        End If
    End If
    If [typb_1] = "Solid Round" Then
        If [stack_1] = "Yes" Then
            TopPart.Cut ([G22:I25])
            RoundB.Cut ([G26:I27])
            CompCheck.Cut ([G29:I34])
        End If
        If [stack_1] = "No" Then
            TopPart.Cut ([G22:I25])
            Offset.Cut ([G26:I26])
            RoundB.Cut ([G27:I28])
            CompCheck.Cut ([G30:I35])
        End If
    End If
End If
If [typa_1] = "Solid Round" Then
    If [typb_1] = "Channel" Then
        If [stack_1] = "Yes" Then
            TopPart.Cut ([G21:I24])
            ChannelB.Cut ([G25:I26])
            CompCheck.Cut ([G28:I33])
        End If
        If [stack_1] = "No" Then
            TopPart.Cut ([G21:I24])
            Offset.Cut ([G25:I25])
            ChannelB.Cut ([G26:I27])
            CompCheck.Cut ([G29:I34])
        End If
    End If
    If [typb_1] = "Plate" Then
        If [stack_1] = "Yes" Then
            TopPart.Cut ([G21:I24])
            PlateB.Cut ([G25:I27])
            CompCheck.Cut ([G29:I34])
        End If
        If [stack_1] = "No" Then
            TopPart.Cut ([G21:I24])
            Offset.Cut ([G25:I25])
            PlateB.Cut ([G26:I28])
            CompCheck.Cut ([G30:I35])
        End If
    End If
    If [typb_1] = "Solid Round" Then
        If [stack_1] = "Yes" Then
            TopPart.Cut ([G21:I24])
            RoundB.Cut ([G25:I26])
            CompCheck.Cut ([G28:I33])
        End If
        If [stack_1] = "No" Then
            TopPart.Cut ([G21:I24])
            Offset.Cut ([G25:I25])
            RoundB.Cut ([G26:I27])
            CompCheck.Cut ([G29:I34])
        End If
    End If
End If
End Sub
:confused:
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
I think that you need to turn off events to stop your code being triggered over and over:

Rich (BB code): 
Application.EnableEvents = False
If Not Application.Intersect(Trigger1a, Range(Target.Address)) _
Is Nothing Then FormatReinforcement1A
'If cells other than "numtyp_1" are changed, do nothing, otherwise
'run macros based on whether numtyp is specified as 1 or 2
If Not Application.Intersect(Trigger1b, Range(Target.Address)) _
Is Nothing Then
If [numtyp_1] = 1 Then FormatReinforcement1Num1
If [numtyp_1] = 2 Then FormatReinforcement1Num2
End If
'If cells other than "typb_1" are changed, do nothing,,
'otherwise run the appropriate macro
If Not Application.Intersect(Trigger1c, Range(Target.Address)) _
Is Nothing Then FormatReinforcement1B
Application.ScreenUpdating = Old_ScrUpdate
Application.EnableEvents = True
 
Upvote 0
Thanks, I'll try that. Also, as per usual, I figured out at least part of my problem shortly after asking the question. The macro that was causing excel to lock up was trying to move the cell that triggered it around thus calling itself ad infinitum. It would figure that after staring at it for hours it would be something stupid like that.

I'm still open to advice on generally making it more efficient though. Would Cases be more appropriate than a bunch of nested IFs in this case? I've never used Cases before and the IF statements I have in there now work, but I was wondering if Cases might be more efficient. Thanks though, I'll go add the disable/enable events code and report back.
 
Upvote 0
Worked perfectly! Thank you. Could you explain why that worked/what exactly that code does to prevent the repeated macro triggering that was going on? I know it solves my problem in this case, but I'm curious so I have a better idea of when I can/should use it in the future.
 
Upvote 0
Every time something is changed on the sheet it fires the Worksheet_Change macro. So if your subroutine is copying and pasting to the sheet it will keep triggering the Worksheet_Change code. Turning off events will stop this from happening but you need to turn them on again before your Worksheet_Change code finishes.
 
Upvote 0
Ah I see, thanks for explaining, that definitely makes sense and I'm sure it'll come in handy in the future!
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Count - specific consecutive order
Replies
6
Views
545
Peter_SSs
Peter_SSs
A
  • Question
Finance P/L and Day P/L Formula
Replies
1
Views
129
awoohaw
awoohaw
R
  • Question
Vlookup
Replies
4
Views
367
Robert Wyatt
R
R
  • Solved
Overtime Formula
Replies
2
Views
272
Dave Patton
D
L
  • Solved
Help with IF Formula for my Data.
Replies
5
Views
365
Lukma
L

Forum statistics

Threads
1,215,523
Messages
6,125,323
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top