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

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

Hi, i edited your post to prevent any confusion by anyone.. Will try your code now! Thanks!
@user1204868: You mean 'your' post :) I have made those changes in my code as well.
yup.. mine and also yours.. the first edited section of your answer. :).. Thanks! It works well! :)
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
@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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related