Userform freezing up, cannot identify the issue

K

Kuljack

Active Member
Joined
Aug 14, 2015
Messages
327
Hello all,

I am experiencing a strange issue with the use of a UserForm. I have just started experimenting with these and have found them very useful for organizing data efficiently; however, I have run into a reoccurring issue with one of my UserForms where it continues to lock up.

What I am doing:

I have a worksheet with a table full of data. This data is 77 columns deep, of which 68 are used/updated and the other 9 are active formulas to assist with providing status updates based on the data in the other 68 columns.

This data is essentially a pipeline of data that needs managed daily, but scrolling left/right on this worksheet is cumbersome and inefficient. So on a separate worksheet I have laid out pertinent high level sections of the data, much like a briefing. Each row of data is categorized by a series number. For example, if I wanted to review a certain item from the pipeline I would say I was to look at series 12345.

I have it where I enter 12345 into a cell on the 2nd worksheet (calling this the Manager Tab going forward), and run a macro that will take that value (12345) and reference the worksheet with data (Data tab going forward) to find the matching value, pull the value from each of the 68 columns mentioned before in the respected row and then carry this information over to the Manager tab where they are laid out appropriately.

I then have a macro/link to open the userform and manage this series pulled from the other worksheet, and once updates are completed it writes back over to the Data tab. On the Userform, I decided to add a button to call the same macro initially being run before the Userform was opened, simple If clicked - call macro.​


Where I am running into an issue:

Certain series when pulled from the Data tab, freeze up when in the Userform. If pulled using the macro while the Userform is not shown, do fine. As soon as I open the Userform though, everything freezes up. I am then forced to end the session and reopen Excel.

So far, I have troubleshot several ideas with nothing providing success.

1. I have attempted pulling the series from the Raw data within the Userform, freezes up
2. I have attempted pulling the series from the Raw data first, then opening the Userform, freezes up
3. I have compared series that cause the system to freeze to those that have no issues
a. Formatting matches on all accounts
b. Nothing regarding the cell values varies
4. I have researched a number of other posts with similar issues, nothing appears to relate to me scenario​

A few other details that may be relevant to the design:

I have several textboxes within the Userform that have the following settings:

1. Multiline: True
2. EnterKeyBehavior: True
3. ScrollBars: 2- fmScrollBarsVertical

(Unsure if these settings cause issues when transferring back to the cells in the worksheet due to formatting variances. I have also experimented with the cells on the worksheet that these textboxes are linked to by flipping between wrap text on/off)

Below I am providing the code the resides within this effort:

Code that pulls the CCR
Code: 
Sub PullCCR()

Call ClearAll

Dim Facilitator As String
Dim CCR As String
Dim CCRname As String
Dim RiskLevel As String
Dim FastTrack As String
Dim Surf_WA As String
Dim CO As String
Dim RequestType As String
Dim SubDepartment As String
Dim NewChangeDate As String
Dim PreLimSubmit As String
Dim PreLimApproved As String
Dim CFITcomplete As String
Dim CFITresolved As String
Dim ORCname As String
Dim PRVapproved As String
Dim FRVrequested As String
Dim FRVapproved As String
Dim VTCrequested As String
Dim VTCapproved As String
Dim RECOR As String
Dim RECORdate As String
Dim Legal As String
Dim Legaldate As String
Dim CreditPolicy As String
Dim CreditPolicydate As String
Dim Finance As String
Dim Financedate As String
Dim TOG As String
Dim TOGdate As String
Dim ServicingFee As String
Dim ServicingFeeDate As String
Dim CAGTF As String
Dim CAGTFdate As String
Dim FRLUDAAP As String
Dim FRLUDAAPdate As String
Dim CAD As String
Dim CADcomplete As String
Dim SVPapproval As String
Dim SVPdate As String
Dim OOC As String
Dim OOCdate As String
Dim TG1ready As String
Dim TG1meeting As String
Dim TG1approved As String
Dim TG2ready As String
Dim TG2meeting As String
Dim TG2approved As String
Dim Implement As String
Dim CloseReady As String
Dim Closed As String
Dim Repository As String
Dim LastFollowup As String
Dim Status As String
Dim Comments As String
Dim NextFollowup As String
Dim r1 As String
Dim r7 As String
Dim r8 As String
Dim r10 As String
Dim i11 As String
Dim i21 As String
Dim i22 As String
Dim CS As String
Dim ES As String
Dim PG As String
Dim PI As String
Dim CoChangeOwner As String

Dim CCRpull As String
Dim Ticketpull As String


Application.ScreenUpdating = False

Sheets("CCR Manager").Select

CCRpull = Range("E1")

Ticket = MsgBox("Is there a ticket# [SURF/WA#]?", vbYesNo)
    
    If Ticket = vbYes Then
        Ticketpull = InputBox("What is the ticket#?")
        If Ticketpull = vbCancel Then
            Exit Sub
        Else
        Sheets("CCR data").Select
        
        For Each xCCR In Sheets("CCR data").Range(Cells(1, "B"), Cells(1, "B").End(xlDown))
            CCR = Cells(xCCR.Row, 2)
            Ticket = Cells(xCCR.Row, 6)
            If CCR = CCRpull Then
                If Ticket = Ticketpull Then
                    Facilitator = Cells(xCCR.Row, 1)
                    CCR = Cells(xCCR.Row, 2)
                    CCRname = Cells(xCCR.Row, 3)
                    RiskLevel = Cells(xCCR.Row, 4)
                    FastTrack = Cells(xCCR.Row, 5)
                    Surf_WA = Cells(xCCR.Row, 6)
                    CO = Cells(xCCR.Row, 7)
                    RequestType = Cells(xCCR.Row, 8)
                    SubDepartment = Cells(xCCR.Row, 9)
                    NewChangeDate = Cells(xCCR.Row, 10)
                    PreLimSubmit = Cells(xCCR.Row, 11)
                    PreLimApproved = Cells(xCCR.Row, 12)
                    CFITcomplete = Cells(xCCR.Row, 13)
                    CFITresolved = Cells(xCCR.Row, 14)
                    ORCname = Cells(xCCR.Row, 15)
                    PRVapproved = Cells(xCCR.Row, 16)
                    FRVrequested = Cells(xCCR.Row, 17)
                    FRVapproved = Cells(xCCR.Row, 18)
                    VTCrequested = Cells(xCCR.Row, 19)
                    VTCapproved = Cells(xCCR.Row, 20)
                    RECOR = Cells(xCCR.Row, 21)
                    RECORdate = Cells(xCCR.Row, 22)
                    Legal = Cells(xCCR.Row, 23)
                    Legaldate = Cells(xCCR.Row, 24)
                    CreditPolicy = Cells(xCCR.Row, 25)
                    CreditPolicydate = Cells(xCCR.Row, 26)
                    Finance = Cells(xCCR.Row, 27)
                    Financedate = Cells(xCCR.Row, 28)
                    TOG = Cells(xCCR.Row, 29)
                    TOGdate = Cells(xCCR.Row, 30)
                    ServicingFee = Cells(xCCR.Row, 31)
                    ServicingFeeDate = Cells(xCCR.Row, 32)
                    CAGTF = Cells(xCCR.Row, 33)
                    CAGTFdate = Cells(xCCR.Row, 34)
                    FRLUDAAP = Cells(xCCR.Row, 35)
                    FRLUDAAPdate = Cells(xCCR.Row, 36)
                    CAD = Cells(xCCR.Row, 37)
                    CADcomplete = Cells(xCCR.Row, 38)
                    SVPapproval = Cells(xCCR.Row, 39)
                    SVPdate = Cells(xCCR.Row, 40)
                    OOC = Cells(xCCR.Row, 41)
                    OOCdate = Cells(xCCR.Row, 42)
                    TG1ready = Cells(xCCR.Row, 43)
                    TG1meeting = Cells(xCCR.Row, 44)
                    TG1approved = Cells(xCCR.Row, 45)
                    TG2ready = Cells(xCCR.Row, 46)
                    TG2meeting = Cells(xCCR.Row, 47)
                    TG2approved = Cells(xCCR.Row, 48)
                    Implement = Cells(xCCR.Row, 49)
                    CloseReady = Cells(xCCR.Row, 50)
                    Closed = Cells(xCCR.Row, 51)
                    Repository = Cells(xCCR.Row, 52)
                    LastFollowup = Cells(xCCR.Row, 53)
                    Status = Cells(xCCR.Row, 54)
                    Comments = Cells(xCCR.Row, 55)
                    NextFollowup = Cells(xCCR.Row, 56)
                    r1 = Cells(xCCR.Row, 57)
                    r7 = Cells(xCCR.Row, 58)
                    r8 = Cells(xCCR.Row, 59)
                    r10 = Cells(xCCR.Row, 60)
                    i11 = Cells(xCCR.Row, 61)
                    i21 = Cells(xCCR.Row, 62)
                    i22 = Cells(xCCR.Row, 63)
                    CoChangeOwner = Cells(xCCR.Row, 64)
                    CS = Cells(xCCR.Row, 65)
                    ES = Cells(xCCR.Row, 66)
                    PG = Cells(xCCR.Row, 68)
                    PI = Cells(xCCR.Row, 69)
                     
                    Sheets("CCR Manager").Select
                    
                        Range("D17").Value = Facilitator
                        Range("E17").Value = CCR
                        Range("F17").Value = CCRname
                        Range("G17").Value = RiskLevel
                        Range("H17").Value = FastTrack
                        Range("I17").Value = Surf_WA
                        Range("J17").Value = CO
                        Range("K17").Value = RequestType
                        Range("L17").Value = SubDepartment
                        Range("M17").Value = NewChangeDate
                        Range("N17").Value = PreLimSubmit
                        Range("O17").Value = PreLimApproved
                        Range("P17").Value = CFITcomplete
                        Range("Q17").Value = CFITresolved
                        Range("R17").Value = ORCname
                        Range("S17").Value = PRVapproved
                        Range("T17").Value = FRVrequested
                        Range("U17").Value = FRVapproved
                        Range("V17").Value = VTCrequested
                        Range("W17").Value = VTCapproved
                        Range("X17").Value = RECOR
                        Range("Y17").Value = RECORdate
                        Range("Z17").Value = Legal
                        Range("AA17").Value = Legaldate
                        Range("AB17").Value = CreditPolicy
                        Range("AC17").Value = CreditPolicydate
                        Range("AD17").Value = Finance
                        Range("AE17").Value = Financedate
                        Range("AF17").Value = TOG
                        Range("AG17").Value = TOGdate
                        Range("AH17").Value = ServicingFee
                        Range("AI17").Value = ServicingFeeDate
                        Range("AJ17").Value = CAGTF
                        Range("AK17").Value = CAGTFdate
                        Range("AL17").Value = FRLUDAAP
                        Range("AM17").Value = FRLUDAAPdate
                        Range("AN17").Value = CAD
                        Range("AO17").Value = CADcomplete
                        Range("AP17").Value = SVPapproval
                        Range("AQ17").Value = SVPdate
                        Range("AR17").Value = OOC
                        Range("AS17").Value = OOCdate
                        Range("AT17").Value = TG1ready
                        Range("AU17").Value = TG1meeting
                        Range("AV17").Value = TG1approved
                        Range("AW17").Value = TG2ready
                        Range("AX17").Value = TG2meeting
                        Range("AY17").Value = TG2approved
                        Range("AZ17").Value = Implement
                        Range("BA17").Value = CloseReady
                        Range("BB17").Value = Closed
                        Range("BC17").Value = Repository
                        Range("BD17").Value = LastFollowup
                        Range("BE17").Value = Status
                        Range("I1").Value = Status
                        Range("BF17").Value = Comments
                        Range("BG17").Value = NextFollowup
                        Range("BH17").Value = r1
                        Range("BI17").Value = r7
                        Range("BJ17").Value = r8
                        Range("BK17").Value = r10
                        Range("BL17").Value = i11
                        Range("BM17").Value = i21
                        Range("BN17").Value = i22
                        Range("BO17").Value = CoChangeOwner
                        Range("BP17").Value = CS
                        Range("BQ17").Value = ES
                        Range("BR17").Value = PG
                        Range("BS17").Value = PI
                Else
                End If
            Else
            End If
        Next
        End If
    ElseIf Ticket = vbNo Then
    
        Sheets("CCR data").Select
        
            For Each xCCR In Sheets("CCR data").Range(Cells(1, "B"), Cells(1, "B").End(xlDown))
                CCR = Cells(xCCR.Row, 2)
                If CCR = CCRpull Then
                    Facilitator = Cells(xCCR.Row, 1)
                    CCR = Cells(xCCR.Row, 2)
                    CCRname = Cells(xCCR.Row, 3)
                    RiskLevel = Cells(xCCR.Row, 4)
                    FastTrack = Cells(xCCR.Row, 5)
                    Surf_WA = Cells(xCCR.Row, 6)
                    CO = Cells(xCCR.Row, 7)
                    RequestType = Cells(xCCR.Row, 8)
                    SubDepartment = Cells(xCCR.Row, 9)
                    NewChangeDate = Cells(xCCR.Row, 10)
                    PreLimSubmit = Cells(xCCR.Row, 11)
                    PreLimApproved = Cells(xCCR.Row, 12)
                    CFITcomplete = Cells(xCCR.Row, 13)
                    CFITresolved = Cells(xCCR.Row, 14)
                    ORCname = Cells(xCCR.Row, 15)
                    PRVapproved = Cells(xCCR.Row, 16)
                    FRVrequested = Cells(xCCR.Row, 17)
                    FRVapproved = Cells(xCCR.Row, 18)
                    VTCrequested = Cells(xCCR.Row, 19)
                    VTCapproved = Cells(xCCR.Row, 20)
                    RECOR = Cells(xCCR.Row, 21)
                    RECORdate = Cells(xCCR.Row, 22)
                    Legal = Cells(xCCR.Row, 23)
                    Legaldate = Cells(xCCR.Row, 24)
                    CreditPolicy = Cells(xCCR.Row, 25)
                    CreditPolicydate = Cells(xCCR.Row, 26)
                    Finance = Cells(xCCR.Row, 27)
                    Financedate = Cells(xCCR.Row, 28)
                    TOG = Cells(xCCR.Row, 29)
                    TOGdate = Cells(xCCR.Row, 30)
                    ServicingFee = Cells(xCCR.Row, 31)
                    ServicingFeeDate = Cells(xCCR.Row, 32)
                    CAGTF = Cells(xCCR.Row, 33)
                    CAGTFdate = Cells(xCCR.Row, 34)
                    FRLUDAAP = Cells(xCCR.Row, 35)
                    FRLUDAAPdate = Cells(xCCR.Row, 36)
                    CAD = Cells(xCCR.Row, 37)
                    CADcomplete = Cells(xCCR.Row, 38)
                    SVPapproval = Cells(xCCR.Row, 39)
                    SVPdate = Cells(xCCR.Row, 40)
                    OOC = Cells(xCCR.Row, 41)
                    OOCdate = Cells(xCCR.Row, 42)
                    TG1ready = Cells(xCCR.Row, 43)
                    TG1meeting = Cells(xCCR.Row, 44)
                    TG1approved = Cells(xCCR.Row, 45)
                    TG2ready = Cells(xCCR.Row, 46)
                    TG2meeting = Cells(xCCR.Row, 47)
                    TG2approved = Cells(xCCR.Row, 48)
                    Implement = Cells(xCCR.Row, 49)
                    CloseReady = Cells(xCCR.Row, 50)
                    Closed = Cells(xCCR.Row, 51)
                    Repository = Cells(xCCR.Row, 52)
                    LastFollowup = Cells(xCCR.Row, 53)
                    Status = Cells(xCCR.Row, 54)
                    Comments = Cells(xCCR.Row, 55)
                    NextFollowup = Cells(xCCR.Row, 56)
                    r1 = Cells(xCCR.Row, 57)
                    r7 = Cells(xCCR.Row, 58)
                    r8 = Cells(xCCR.Row, 59)
                    r10 = Cells(xCCR.Row, 60)
                    i11 = Cells(xCCR.Row, 61)
                    i21 = Cells(xCCR.Row, 62)
                    i22 = Cells(xCCR.Row, 63)
                    CoChangeOwner = Cells(xCCR.Row, 64)
                    CS = Cells(xCCR.Row, 65)
                    ES = Cells(xCCR.Row, 66)
                    PG = Cells(xCCR.Row, 68)
                    PI = Cells(xCCR.Row, 69)
                 
            Sheets("CCR Manager").Select
            
                Range("D17").Value = Facilitator
                Range("E17").Value = CCR
                Range("F17").Value = CCRname
                Range("G17").Value = RiskLevel
                Range("H17").Value = FastTrack
                Range("I17").Value = Surf_WA
                Range("J17").Value = CO
                Range("K17").Value = RequestType
                Range("L17").Value = SubDepartment
                Range("M17").Value = NewChangeDate
                Range("N17").Value = PreLimSubmit
                Range("O17").Value = PreLimApproved
                Range("P17").Value = CFITcomplete
                Range("Q17").Value = CFITresolved
                Range("R17").Value = ORCname
                Range("S17").Value = PRVapproved
                Range("T17").Value = FRVrequested
                Range("U17").Value = FRVapproved
                Range("V17").Value = VTCrequested
                Range("W17").Value = VTCapproved
                Range("X17").Value = RECOR
                Range("Y17").Value = RECORdate
                Range("Z17").Value = Legal
                Range("AA17").Value = Legaldate
                Range("AB17").Value = CreditPolicy
                Range("AC17").Value = CreditPolicydate
                Range("AD17").Value = Finance
                Range("AE17").Value = Financedate
                Range("AF17").Value = TOG
                Range("AG17").Value = TOGdate
                Range("AH17").Value = ServicingFee
                Range("AI17").Value = ServicingFeeDate
                Range("AJ17").Value = CAGTF
                Range("AK17").Value = CAGTFdate
                Range("AL17").Value = FRLUDAAP
                Range("AM17").Value = FRLUDAAPdate
                Range("AN17").Value = CAD
                Range("AO17").Value = CADcomplete
                Range("AP17").Value = SVPapproval
                Range("AQ17").Value = SVPdate
                Range("AR17").Value = OOC
                Range("AS17").Value = OOCdate
                Range("AT17").Value = TG1ready
                Range("AU17").Value = TG1meeting
                Range("AV17").Value = TG1approved
                Range("AW17").Value = TG2ready
                Range("AX17").Value = TG2meeting
                Range("AY17").Value = TG2approved
                Range("AZ17").Value = Implement
                Range("BA17").Value = CloseReady
                Range("BB17").Value = Closed
                Range("BC17").Value = Repository
                Range("BD17").Value = LastFollowup
                Range("BE17").Value = Status
                Range("I1").Value = Status
                Range("BF17").Value = Comments
                Range("BG17").Value = NextFollowup
                Range("BH17").Value = r1
                Range("BI17").Value = r7
                Range("BJ17").Value = r8
                Range("BK17").Value = r10
                Range("BL17").Value = i11
                Range("BM17").Value = i21
                Range("BN17").Value = i22
                Range("BO17").Value = CoChangeOwner
                Range("BP17").Value = CS
                Range("BQ17").Value = ES
                Range("BR17").Value = PG
                Range("BS17").Value = PI
                Else
                End If
            Next
    Else
    End If
    
'Highlights empty cells/white-out populated cells
For Each Y In UserForm2.Controls
    If TypeName(Y) = "TextBox" Then
        If Y.Value = "" Then
            Y.BackColor = &H80FFFF
        Else
            Y.BackColor = &H80000005
        End If
    End If
Next Y


Application.ScreenUpdating = True

End Sub

Code that writes the updates back to the data tab, some value references change as there are formulas on the manager tab that look at update values, run some logic, and provide new dates and/or yes/no remarks:
Code: 
Sub UpdateCCR()

    Dim Facilitator As String
    Dim CCR As String
    Dim CCRname As String
    Dim RiskLevel As String
    Dim FastTrack As String
    Dim Surf_WA As String
    Dim CO As String
    Dim RequestType As String
    Dim SubDepartment As String
    Dim NewChangeDate As String
    Dim PreLimSubmit As String
    Dim PreLimApproved As String
    Dim CFITcomplete As String
    Dim CFITresolved As String
    Dim ORCname As String
    Dim PRVapproved As String
    Dim FRVrequested As String
    Dim FRVapproved As String
    Dim VTCrequested As String
    Dim VTCapproved As String
    Dim RECOR As String
    Dim RECORdate As String
    Dim Legal As String
    Dim Legaldate As String
    Dim CreditPolicy As String
    Dim CreditPolicydate As String
    Dim Finance As String
    Dim Financedate As String
    Dim TOG As String
    Dim TOGdate As String
    Dim ServicingFee As String
    Dim ServicingFeeDate As String
    Dim CAGTF As String
    Dim CAGTFdate As String
    Dim FRLUDAAP As String
    Dim FRLUDAAPdate As String
    Dim CAD As String
    Dim CADcomplete As String
    Dim SVPapproval As String
    Dim SVPdate As String
    Dim OOC As String
    Dim OOCdate As String
    Dim TG1ready As String
    Dim TG1meeting As String
    Dim TG1approved As String
    Dim TG2ready As String
    Dim TG2meeting As String
    Dim TG2approved As String
    Dim Implement As String
    Dim CloseReady As String
    Dim Closed As String
    Dim Repository As String
    Dim LastFollowup As String
    Dim Status As String
    Dim Comments As String
    Dim NextFollowup As String
    Dim r1 As String
    Dim r7 As String
    Dim r8 As String
    Dim r10 As String
    Dim i11 As String
    Dim i21 As String
    Dim i22 As String
    Dim CS As String
    Dim ES As String
    Dim PG As String
    Dim PI As String
    
    Dim CoChangeOwner As String
    
    Dim CCRmatch As String
    Dim TicketMatch As String
    

Application.ScreenUpdating = False

        Sheets("CCR Manager").Select
    
        Facilitator = Range("D17").Value
        CCR = Range("E1").Value
        CCRname = Range("F17").Value
        RiskLevel = Range("G17").Value
        FastTrack = Range("H17").Value
        Surf_WA = Range("I17").Value
        CO = Range("J17").Value
        RequestType = Range("K17").Value
        SubDepartment = Range("L17").Value
        NewChangeDate = Range("M17").Value
        PreLimSubmit = Range("N17").Value
        PreLimApproved = Range("O17").Value
        CFITcomplete = Range("AD5").Value
        CFITresolved = Range("Q17").Value
        ORCname = Range("R17").Value
        PRVapproved = Range("S17").Value
        FRVrequested = Range("T17").Value
        FRVapproved = Range("U17").Value
        VTCrequested = Range("V17").Value
        VTCapproved = Range("W17").Value
        RECOR = Range("X17").Value
        RECORdate = Range("Y17").Value
        Legal = Range("Z17").Value
        Legaldate = Range("AA17").Value
        CreditPolicy = Range("AB17").Value
        CreditPolicydate = Range("AC17").Value
        Finance = Range("AD17").Value
        Financedate = Range("AE17").Value
        TOG = Range("AF17").Value
        TOGdate = Range("AG17").Value
        ServicingFee = Range("AH17").Value
        ServicingFeeDate = Range("AI17").Value
        CAGTF = Range("AJ17").Value
        CAGTFdate = Range("AK17").Value
        FRLUDAAP = Range("AL17").Value
        FRLUDAAPdate = Range("AM17").Value
        CAD = Range("AD11").Value
        CADcomplete = Range("AO17").Value
        SVPapproval = Range("AP17").Value
        SVPdate = Range("AQ17").Value
        OOC = Range("AR17").Value
        OOCdate = Range("AS17").Value
        TG1ready = Range("AD8").Value
        TG1meeting = Range("AU17").Value
        TG1approved = Range("AV17").Value
        TG2ready = Range("AE8").Value
        TG2meeting = Range("AX17").Value
        TG2approved = Range("AY17").Value
        Implement = Range("AZ17").Value
        CloseReady = Range("BA17").Value
        Closed = Range("BB17").Value
        Repository = Range("BC17").Value
        LastFollowup = Range("T1").Value
        Status = Range("I1").Value
        Comments = Range("BF17").Value
        NextFollowup = Range("M3").Value
        r1 = Range("BH17").Value
        r7 = Range("BI17").Value
        r8 = Range("BJ17").Value
        r10 = Range("BK17").Value
        i11 = Range("BL17").Value
        i21 = Range("BM17").Value
        i22 = Range("BN17").Value
        CoChangeOwner = Range("BO17").Value
        CS = Range("BP17").Value
        ES = Range("BQ17").Value
        PG = Range("AE5").Value
        PI = Range("BS17").Value
        
        
    Sheets("CCR data").Select
    
    For Each xCCR In Sheets("CCR data").Range(Cells(1, "B"), Cells(1, "B").End(xlDown))
        CCRmatch = Cells(xCCR.Row, 2)
        If CCRmatch = CCR Then
            TicketMatch = Cells(xCCR.Row, 6)
            If TicketMatch = Surf_WA Then
                Cells(xCCR.Row, 2) = CCR
                Cells(xCCR.Row, 3) = CCRname
                Cells(xCCR.Row, 4) = RiskLevel
                Cells(xCCR.Row, 5) = FastTrack
                Cells(xCCR.Row, 6) = Surf_WA
                Cells(xCCR.Row, 7) = CO
                Cells(xCCR.Row, 8) = RequestType
                Cells(xCCR.Row, 9) = SubDepartment
                Cells(xCCR.Row, 10) = NewChangeDate
                Cells(xCCR.Row, 11) = PreLimSubmit
                Cells(xCCR.Row, 12) = PreLimApproved
                Cells(xCCR.Row, 13) = CFITcomplete
                Cells(xCCR.Row, 14) = CFITresolved
                Cells(xCCR.Row, 15) = ORCname
                Cells(xCCR.Row, 16) = PRVapproved
                Cells(xCCR.Row, 17) = FRVrequested
                Cells(xCCR.Row, 18) = FRVapproved
                Cells(xCCR.Row, 19) = VTCrequested
                Cells(xCCR.Row, 20) = VTCapproved
                Cells(xCCR.Row, 21) = RECOR
                Cells(xCCR.Row, 22) = RECORdate
                Cells(xCCR.Row, 23) = Legal
                Cells(xCCR.Row, 24) = Legaldate
                Cells(xCCR.Row, 25) = CreditPolicy
                Cells(xCCR.Row, 26) = CreditPolicydate
                Cells(xCCR.Row, 27) = Finance
                Cells(xCCR.Row, 28) = Financedate
                Cells(xCCR.Row, 29) = TOG
                Cells(xCCR.Row, 30) = TOGdate
                Cells(xCCR.Row, 31) = ServicingFee
                Cells(xCCR.Row, 32) = ServicingFeeDate
                Cells(xCCR.Row, 33) = CAGTF
                Cells(xCCR.Row, 34) = CAGTFdate
                Cells(xCCR.Row, 35) = FRLUDAAP
                Cells(xCCR.Row, 36) = FRLUDAAPdate
                Cells(xCCR.Row, 37) = CAD
                Cells(xCCR.Row, 38) = CADcomplete
                Cells(xCCR.Row, 39) = SVPapproval
                Cells(xCCR.Row, 40) = SVPdate
                Cells(xCCR.Row, 41) = OOC
                Cells(xCCR.Row, 42) = OOCdate
                Cells(xCCR.Row, 43) = TG1ready
                Cells(xCCR.Row, 44) = TG1meeting
                Cells(xCCR.Row, 45) = TG1approved
                Cells(xCCR.Row, 46) = TG2ready
                Cells(xCCR.Row, 47) = TG2meeting
                Cells(xCCR.Row, 48) = TG2approved
                Cells(xCCR.Row, 49) = Implement
                Cells(xCCR.Row, 50) = CloseReady
                Cells(xCCR.Row, 51) = Closed
                Cells(xCCR.Row, 52) = Repository
                Cells(xCCR.Row, 53) = LastFollowup
                Cells(xCCR.Row, 54) = Status
                Cells(xCCR.Row, 55) = Comments
                Cells(xCCR.Row, 56) = NextFollowup
                Cells(xCCR.Row, 57) = r1
                Cells(xCCR.Row, 58) = r7
                Cells(xCCR.Row, 59) = r8
                Cells(xCCR.Row, 60) = r10
                Cells(xCCR.Row, 61) = i11
                Cells(xCCR.Row, 62) = i21
                Cells(xCCR.Row, 63) = i22
                Cells(xCCR.Row, 64) = CoChangeOwner
                Cells(xCCR.Row, 65) = CS
                Cells(xCCR.Row, 66) = ES
                Cells(xCCR.Row, 68) = PG
                Cells(xCCR.Row, 69) = PI

            Else
            End If
        Else
        End If

    
    
    
    Next
    
Application.ScreenUpdating = True

    
Sheets("CCR Manager").Select

End Sub


Code within UserForm, only providing those with code:
Code: 
Private Sub ComboBox14_Change()
    Dim A As Integer
    A = ComboBox14.ListIndex
    Select Case A
        Case Is = 0
            MsgBox "Prompts text"
    End Select
End Sub
Private Sub ComboBox15_Change()
    Dim B As Integer
    B = ComboBox15.ListIndex
    Select Case B
        Case Is = 0
            MsgBox "Prompts text"
    End Select
End Sub
Private Sub ComboBox16_Change()
    Dim C As Integer
    C = ComboBox16.ListIndex
    Select Case C
        Case Is = 0
            MsgBox "Prompts text"
    End Select
End Sub
Private Sub ComboBox17_Change()
    Dim D As Integer
    D = ComboBox17.ListIndex
    Select Case D
        Case Is = 0
            MsgBox "Prompts text"
    End Select
End Sub
Private Sub ComboBox18_Change()
    Dim E As Integer
    E = ComboBox18.ListIndex
    Select Case E
        Case Is = 0
            MsgBox "Prompts text"
    End Select
End Sub
Private Sub ComboBox19_Change()
    Dim F As Integer
    F = ComboBox19.ListIndex
    Select Case F
        Case Is = 0
            MsgBox "Prompts text"
    End Select
End Sub
Private Sub ComboBox20_Change()
    Dim G As Integer
    G = ComboBox20.ListIndex
    Select Case G
        Case Is = 0
            MsgBox "Prompts text"
    End Select
End Sub
Private Sub CommandButton1_Click()
    Call PullCCR
End Sub
Private Sub CommandButton17_Click()
    Dim RetCode As Integer
    RetCode = Shell("C:\Program Files\Internet Explorer\IEXPLORE.EXE http://hcfg-portal.homestead.wellsfargo.com/sites/ccp/User%20Roles%20and/Forms/AllItems.aspx", vbNormalFocus)
        If RetCode = 0 Then
            MsgBox "Could not open webpage."
        End If
End Sub
Private Sub CommandButton19_Click()
    Range("b7").Value = "(All)"
    Range("b8").Value = "(All)"
    Range("b9").Value = "(All)"
    Range("b10").Value = "(All)"
    Range("b11").Value = "(All)"
    Range("b12").Value = "(All)"
    Range("b13").Value = "(All)"
    Range("b14").Value = "(All)"
    ActiveWorkbook.RefreshAll
End Sub
Private Sub CommandButton2_Click()
'Save & Clear
    Call UpdateCCR
    Call ClearAll
    ActiveWorkbook.RefreshAll
End Sub
Private Sub CommandButton20_Click()
    ActiveWorkbook.RefreshAll
End Sub
Private Sub CommandButton21_Click()
'Close without Save
    UserForm2.Hide
End Sub
Private Sub CommandButton3_Click()
'Save & Continue
    Call UpdateCCR
    ActiveWorkbook.RefreshAll
End Sub
Private Sub CommandButton5_Click()
'Save & Close
    Call UpdateCCR
    UserForm2.Hide
    ActiveWorkbook.RefreshAll
End Sub
Private Sub CommandButton6_Click()
    UserForm3.Show
End Sub
Private Sub CommandButton7_Click()
    UserForm4.Show
End Sub
Private Sub CommandButton8_Click()
    Range("AD2").Copy
End Sub
Private Sub CommandButton9_Click()
    UserForm5.Show
End Sub
Private Sub TextBox50_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Call Setdate
End Sub

Code on Sheet that triggers macros when the "hyperlink" is selected on the manager page:
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRange As Range
Dim myRange2 As Range
Dim myRange3 As Range
Dim myRange4 As Range
Dim myRange5 As Range
Dim myRange6 As Range
Dim myRange7 As Range
Dim myRange8 As Range
 
    Set myRange = Range("O1")
    Set myRange2 = Range("O2")
    Set myRange3 = Range("O3")
    Set myRange4 = Range("O4")
    Set myRange5 = Range("O5")
    
    Set myRange6 = Range("O6")
    Set myRange7 = Range("O7")
    Set myRange8 = Range("O9")

'VBA script to run macro's off hyperlinks

'Set destination to same cell hyperlink is in


    If Union(Target, myRange).Address = myRange.Address Then
    'Pull CCR
        Call PullCCR
    ElseIf Union(Target, myRange2).Address = myRange2.Address Then
    'Save & Clear
        Call UpdateCCR
        Call ClearAll
    ElseIf Union(Target, myRange3).Address = myRange3.Address Then
    'Save & Continue
        Call UpdateCCR
    ElseIf Union(Target, myRange4).Address = myRange4.Address Then
    'Clear All
            Range("b7").Value = "(All)"
            Range("b8").Value = "(All)"
            Range("b9").Value = "(All)"
            Range("b10").Value = "(All)"
            Range("b11").Value = "(All)"
            Range("b12").Value = "(All)"
            Range("b13").Value = "(All)"
            Range("b14").Value = "(All)"
        ActiveWorkbook.RefreshAll
        Call ClearAll
    ElseIf Union(Target, myRange5).Address = myRange5.Address Then
    'Clear Pivot
            Range("b7").Value = "(All)"
            Range("b8").Value = "(All)"
            Range("b9").Value = "(All)"
            Range("b10").Value = "(All)"
            Range("b11").Value = "(All)"
            Range("b12").Value = "(All)"
            Range("b13").Value = "(All)"
            Range("b14").Value = "(All)"
        ActiveWorkbook.RefreshAll
    ElseIf Union(Target, myRange6).Address = myRange6.Address Then
        ClearAll
    ElseIf Union(Target, myRange7).Address = myRange7.Address Then
        Call Setdate
    ElseIf Union(Target, myRange8).Address = myRange8.Address Then
        Call Mainhub
    End If


End Sub



The behavior is almost as if a macro is caught on a loop, not sure. Any help is greatly appreciated!
 
Sort by date Sort by votes
So I've come to a solution, fingers crossed I am not shooting myself in the foot with that comment, by breaking down the UserForm.

I did a lot of reading on UserForms and best practices and have done the following:

1. Removed the use of MultiLines and EnterKeyBehavior from the text boxes.
2. I've moved all the formula tied to the UserForm into the Userform utilizing the Activate feature, as I do want this to run multiple times swapping between show/hide.
3. I reamed the old code through and through and took a few subtle improvements into account.

A few other changes took place such as combo boxes being converted to textboxes and drop-downs no longer became relevant and such.

Uncertain as to what was the true fix, but it is successfully running without a freeze up. Thank you for anyone who took the time to read this question and spent any time looking for a solution.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Export sheet as word doc
Replies
9
Views
64
Macropod
Macropod
I
  • Solved
Trying to open userform & run code from worksheet but see a compile error message
Replies
4
Views
122
ipbr21054
I
P
  • Solved
VBA Loop and Msgbox issue
Replies
3
Views
123
saboh12617
S
B
  • Question Question
Trying to change shape visibility during worksheet activate event
Replies
1
Views
100
Logit
Logit
I
  • Solved
Compile error Variable not defind message advice
Replies
2
Views
68
ipbr21054
I

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