run time error for userform

Question

I need help with the following wonder if anyone knows what i can do to resolve the problem.

The following will be a userform of how i want it to look like.

enter image description here

What i'm trying to do is to select this test1 that will link with a pivot table where selecting it, will also select test1 on pivot table. Then, I will need to write the name "test1" into another worksheet, called it Sheet2. I'm able to write out a code to do this, but when i try to deselect it, a run time error appears. Was wondering if I need to add in another code for it or there is already an error in my code.

My code:

Private Sub CheckBox2_Click()
    If CheckBox2.Value = True Then
        Sheets("Sheet1").Select
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Test1")
            .Orientation = xlColumnField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Test1").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
        Sheets("Sheet2").Select
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "Test1"
        Sheets("Sheet1").Select
    End If

    If CheckBox2.Value = False Then
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Test1").Orientation = _
        xlHidden
        Sheets("Sheet2").Select
        Range("A1").Select
        ActiveCell.FormulaR1C1 = ""
    End If
End Sub

It helps us in understanding what you are trying to do in the code if you indent your code :) — Siddharth Rout
– Siddharth Rout, Commented Mar 9, 2012 at 7:48
I think i did indent it, but i left out some.. Sorry for the trouble. — user1204868
– user1204868, Commented Mar 9, 2012 at 8:41

Siddharth Rout · Accepted Answer · 2012-03-09 08:34:45Z

2

Always try to avoid .Select as they are a major cause of pain :) Try this

TRIED AND TESTED

Private Sub CheckBox2_Click()
    Dim ws As Worksheet

    '~~> Sheet1 has the pivot
    Set ws = Sheets("Sheet1")

    If CheckBox2.Value = True Then
        With ws
            With .PivotTables("PivotTable1").PivotFields("Test1")
                .Orientation = xlColumnField
                .Position = 1
            End With
            .PivotTables("PivotTable1").PivotFields("Test1").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
            Sheets("Sheet2").Range("A1").Value = "Test1"
        End With
    Else '<~~ If false
        ws.PivotTables("PivotTable1").PivotFields("Test1").Orientation = xlHidden
        Sheets("Sheet2").Range("A1").ClearContents
    End If
End Sub

EDIT - FOLLOWUP

yup.. test2 can be either on A1 or A2.. but sometimes test1 can be around which will be at A1.. sorry for the additional question – user1204868 30 secs ago

Change this line Sheets("Sheet2").Range("A1").ClearContents to

With Sheets("Sheet2")
    If .Range("A1").Value = "Test2" Then .Range("A1").ClearContents
    If .Range("A2").Value = "Test2" Then .Range("A2").ClearContents
End With

HTH

Sid

edited Mar 9, 2012 at 8:34

answered Mar 9, 2012 at 7:58

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1204868 Over a year ago

Hi, i edited your post to prevent any confusion by anyone.. Will try your code now! Thanks!

Siddharth Rout Over a year ago

@user1204868: You mean 'your' post :) I have made those changes in my code as well.

user1204868 Over a year ago

yup.. mine and also yours.. the first edited section of your answer. :).. Thanks! It works well! :)

user1204868 Over a year ago

just 1 additional question.. What if i have 2 checkboxes and the 2nd one is test2, test2 can happens on either A1 or A2. Then how am i suppoose to clear the contents when it is on either 1? and what if it is on A2 and A1 has a value, test1

Siddharth Rout Over a year ago

@user1204868: Do you mean that clear the cell which has "test2"?

|

Collectives™ on Stack Overflow

run time error for userform

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related