Pivot Table With Tick Boxes And Radio Buttons
Oct 18, 2006
every week i send out a pivot table to different project co-ordinators and would like a way of having simple option button so that they can have the data by "hours" or "costs" and can use tick boxes for the row fields they want to show, for example, "profession", "workstage"," name","cost rate". The page field will always be "Project" and the Column Field will always be "Period" and " Date"
I then want the pivot table to change automatically to their choices. I am pretty basic with VBA so may need explanantions too. It would save me sending out 10 versions of a pivot table all feeding off the same data! They could actually do something themselves! (i know they could drag the fileds they want into the areas they need them but trust me when i say they are not that advanced, they are good at ticking!!)
View 2 Replies
ADVERTISEMENT
Nov 27, 2009
I was wondering why a pivot table will allow you to tick and untick pivot items that are not visible?
My pivot tables have a lot of data and someone may wish to only see the pivot items that are shown in the pivot table in its currrent view. Is there a way in VB to achieve this?
In my attached example when you suntick the Media 'TV' then look in the 'Channels' drop down you can still see the tick boxes for TV channels 'ITV' and 'TV_BBC1' (I know why they are still ticked, I'd just like them not to be visible).
View 3 Replies
View Related
Feb 10, 2012
I am trying to create a worksheet that has multiple radio buttons. Each radio button is linked to a Macro. I need there to be many buttons running down one side of the worksheet. Each button needs to perform a macro that is relevant to the cells in the same row that it is on.
Essentially what i am trying to do is make a macro that when the button is pressed copys data from H6 and paste it into B6. The button is situated above I6. I need a button for each row from 6 to 110.
While this macro is easy to create (i use the record button and then assign the macro to the button), i would have to do this 104 times and assign a new macro to each button.
Is there a quicker way?
View 6 Replies
View Related
Jun 2, 2009
I'm creating a form, that will contain 20-30 tickboxes. Each of these tickboxes will refer to a certain row on a seperate sheet. And it will either hide/un-hide depending on if it is tick/un-ticked.
How can I in the macro know which of the tick boxes that was ticked?
View 11 Replies
View Related
Oct 20, 2009
I have created a Time Sheet for calculating the work hours of employees. There are various criteria which play a role in how work hours and wages earned are calculated (ex. time, over time, LOA, Travel time, stat holidays etc etc.)
In a certain cell I have placed a tick / check box which the user can tick to indicate that that particular day is a statutory holiday. The tick creates a True / False answer in another Linked Cell.
Based on the True / False result I have a formula which, using the IF function with multiple conditions whether the result is True or False, will calculate the hours worked for Regular, Time and Half and Double Time.
The Formula itself works but when I add the condition based on the check box - example: IF(c37=TRUE,...,if(...,if(...,IF(c37=FALSE,...,if(...,if(... and so on, only the TRUE option works. As soon as the check box is "unticked" I get the result "false".
I hope that all makes sense.
If it helps this is my formula... where C37 is the linked cell for the checkbox.
=IF(C37=TRUE,IF(B10<4.1,B11,IF(B10<8.1,4+(B10-B11),IF(B10<12.1,B10-B11+4,IF(B10>12,8,IF(C37=FALSE,IF(B10<8.1,0,IF(B10>8,IF(B10<12.1,B10-B11,4)))))))))
So, when the check box is ticked, the TRUE argument works fine. But when the check box is not ticked I get a "false" result.
View 14 Replies
View Related
Apr 28, 2014
I have set up a spread sheet which uses tick boxes and I have calculated it all up so that the percentage of ticks ticked is displayed in a cell etc. and when I link that data to a bar chart it works perfectly. .e.g as I tick the boxes the bar chart increases
BUT when I try to do the exact same thing with a pie chart it doesn't work.
View 14 Replies
View Related
Apr 8, 2009
I've got a small problem with radio buttons. I've been asked to add a few to a sheet we use at work, problem I'm having is when I add these new buttons from the forms toolbar and then assign the cell link for them some of the other radio buttons on the sheet asume the cell link of the new buttons and when I change them back the cell link for the new buttons change ? i could understand this problem if I had maybe copied them and modified the copies perhaps but that isn't the case.
View 2 Replies
View Related
May 5, 2008
I am having around 80 - 90 radio buttons in my excel page for filling up the forms, but each time i need to give this to a new user i have to reset all the buttons manually, is there any way where in i can do this in one go and all the radio buttons are reset at once without me doing this manually.
View 9 Replies
View Related
Feb 16, 2007
What code do I need to have behind a command button to carry out an action (for example select cell A1 or B1) depending on which one of two radio buttons are selected? I have tried:
Sub Button23_Click()
If Opt1 Then
Range("A1").Select
End If
End Sub
View 9 Replies
View Related
Feb 22, 2013
I have inherited a spreadsheet that has a text box and a radio form that I cannot delete for the life of me. I can't click to select either one nor right click to do anything. I have an "Approve" and "Reject" button on top of them and those work great. But how do I delete the others?
View 1 Replies
View Related
Feb 25, 2013
I am creating scorecards for our company and ratings are inputted using radio buttons (form control). I have a "clear" macro that clears all fields and radio button values (none is ticket). This macro is called throughout different sheets, as different roles in the company have different KPIs and should be on different sheets. I need a code that detects all radio buttons within a certain sheet and clear their values. I cannot put specific radio button names, as I have a lot.
View 2 Replies
View Related
Feb 18, 2014
I need to filter data from row 87 downwards depending on the choices above. Some of the choices could be only one like in the measurement type. That I can take care of but what if the choice is more than one, it could be four and it is not fixed. Is that possible in autofilter? This is when they go to checkboxes where they may have many option. The mounting part is best example of many options. This is the part I can't incorporate to macro. It's somoething dynamic. I can't make my filter autoadjust. So essentially,I wanted to automatically edit the filtering based on those 10 criteria, which is also in ten different columns.Sample work.xlsm
View 1 Replies
View Related
Feb 22, 2013
I have inherited a spreadsheet that has a text box and a radio form that I cannot delete. I can't click to select either one nor right click to do anything. I have an "Approve" and "Reject" button on top of them and those work great. But how do I delete the text box and reject radio button?
View 2 Replies
View Related
Aug 6, 2008
I have a pair of radio buttons on an Excel 2007 sheet. I want the buttons to be clickable, but have the sheet protected.
Both are defined as unlocked. The Group containing the two buttons is also defined as unlocked.
But when I protect the sheet, one of the two buttons gets the pop-up saying the cell is protected - the other button is fine. I've even tried unlocking the cells behind the buttons, but it still pops up.
View 9 Replies
View Related
Oct 20, 2008
I created two radio buttons on a spreadsheet where a user can select
one button for Yes and the other for No. When I right click these
buttons, I notice the names (shown on the left hand side of the
formula bar) are:
Option Button 3 (for No)
Option Button 4 (for Yes)
I'd like to create a macro that automatically selects the Yes or No
button depending on a cell value in another tab. If the cell value is
1, the macro should select Yes. If the value is 0 the macro should
select No.
I tried running the macro below:
Sub testSelectYesOrNo()
If Not IsEmpty(Sheets("Sheet2").Range("A1").Value) Then
Select Case Sheets("Sheet2").Range("A1").Value
Case Is = 1
Sheets("Sheet1").OptionButton4 = True
Case Is = 0
Sheets("Sheet1").OptionButton3 = True
End Select
End If
End Sub
But when this runs I get a "Run-time error 438 Object doesn't support this property or method"
When I click debug, the text "Sheets("Sheet1").OptionButton4 = True"
was highlighted.
View 9 Replies
View Related
Apr 22, 2014
I have a spread sheet with various tick boxes, that when ticked calculate an accumulative percentage in a cell. This cell is the basis of my graph.
E.g. if the cell displays 80% - the chart with show 80% - simple.
However, I want to write a vba code that changes the fill colour of the chart depending on the percentage.
i.e. if the percentage data = 0-49% I wish the chart to display as red. 50-69% - yellow and 70%+ = green.
View 2 Replies
View Related
Apr 3, 2014
I was viewing this thread here: [URL] ...
How to add checkboxes and radio buttons.
View 8 Replies
View Related
Dec 15, 2008
I work in the science field and I'm trying to record an observation with some codes within a template. I have 5 different codes (A-E) to choose from and want to display the code that the person has chosen for each of the 6 compounds they are working with.
I started creating these command radio buttons with some code to display the results in part particular cell, but I would need to do this 30 times linking each group together to get what I want. I know there's an easier way to do this but ....
View 6 Replies
View Related
Feb 14, 2012
just wondering if there's a way i can make the Radio buttons and check-boxes in my forms select-able by hitting enter? My form is quite long and it takes too much time for users to always have to grab the mouse and click the box, if they could just tab to the radio box or check box and hit enter to select it it would be awesome!
View 2 Replies
View Related
Jul 31, 2007
I am trying to make a basic worksheet that will have radio buttons. I have searched around for about 2 hours and can not find any documentation on how exactly to create a buttons and make it do what I want. I have also tried F1 in Excel but that hasn't helped any either. I did stumble uppon a file called "Options.xls" when searching this forum but it does not explain how the buttons were made. The only thing i can figure out how to do is basically insert a button onto the document, and rename the label of the button that's about it. I basically want to create a group of (3) or so buttons. If button A is picked, cell D1 will display "x". If button B is picked, cell D1 will display "y", and so on. Is there a link to an example out there that shows how to create and use buttons?
View 2 Replies
View Related
Jul 23, 2009
I am somewhat of a novice with Pivot Tables. One problem I am having that no one seems able to help me out with is that when I update and refresh my pivot table, it seems like it has a muscle memory with the drop down choice boxes.
For example:
Lets say I have three customers that I have labeled 01 Target, 02 Walmart and 03 Bed Bath and Beyond. If I want to change 01 Target to 02 Target and 02 Walmart to 01 Walmart, it works in the pivot table and shows the proper data but if I try to choose the customer from a drop down box, it remembers the customers as labeled as they were before I made the changes AND the current look.
I will see: 01 Target, 02 Target, 01 Walmart and 02 Walmart in the drop down box but the data will only show up in the pivot table associated with the proper customers as they are labeled in the data source.
View 9 Replies
View Related
Jun 17, 2014
by using radio buttons or checkbox, or anything similar. Based on the answer, I would like cells to appear below the question, for example in B3 another question would be "how many guests would you like to invite?" and B2 would be their answer but is preset to "0"
View 3 Replies
View Related
Sep 6, 2002
I'd like to insert some radio buttons in a worksheet that activate a hyperlink to a web page and then be able to go back to where I started (ie the radio button)
I can link the button to a macro which was a recording of clicking on a hyperlink. The trouble is when I hit the return arrow to return to the sheet from the web page I go back to the location of the hyperlink not where I started from.
Is there a way to do this with the buttons.
If I can rearrange the sheet to allow for more narrow columns I may be able to just use the hyperlink friendly name argument and do it that way but I'm not so sure I can insert more narrow columns without messing up the layout of the whole sheet. With a button I can float several of them over an area regardless of the width of the columns.
View 1 Replies
View Related
Dec 17, 2009
I'm trying to create a form using radio buttons from the "Forms Tool Bar" and not sure I shouldn't be using the "Control Tool Bar"
I've got multiple buttons and wanted to group them so i used the "Group Box" from the Forms tool bar but now there's a box around the buttons (I've turned off Printing for the box) that gets in the way of other text on sheet. I did some searching and thought I read where you could also use the drawing tool bar and draw a rectangle around the buttons which would then let them operate independent of other groups of buttons but that doesn't seem to work so I'm guessing I imagined it. I also noticed that if you don't draw the "Group Box" around the hole radio button box, they don't work with others in the same "Group Box"
I'm not sure if I should use the buttons in the "Control Tool Bar" but I'm thinking I should so that I have the flexibility should I decide to do something different in the future. I'm thinking about using the buttons to hide a sheet as well as select the sheet for printing, but for now one hurdle at a time.
Lastly I tried looking up some information on how to get the "Control Tool Bar" radio button to behave similar to the "Forms" version of the button (i.e. click the button and it appears to alternate from other buttons in the group), however when I place my mouse over the button it selects it rather than letting me change it's state from true to false.
View 8 Replies
View Related
Jul 2, 2009
I have a form that contains a group box with option buttons, and for clarity's sake, next to it another group box, also with option buttons. So, I can choose 1 option in one box and another option in the other box at the same time. I'm sure you heard me coming a mile away, but here it goes anyway: how can I link the two group boxes (and option buttons) so I would only be able to choose 1 option out of both boxes?
View 4 Replies
View Related
Oct 27, 2009
I have a form with at about 1200 checkboxes and I need to make them behave as radio buttons. What I mean is that just one box can be selected in a group of boxes.
Lets say i want to group all 1200 boxes in 400 groups, with 3 check boxes in every group. I want user to be able to select just one check box in every group.
View 12 Replies
View Related
Jan 10, 2007
I was reading your Tick Cell Upon Selection article and it works great. I'm just wondering if there is a way to modify it so that they can only select one of the cells in a row.
For example, in this spreadsheet, the cells I have marked for "tick upon selection" are N8-Q8. In this worksheet, though, I only want the user to be able to select one of the four options. Is there a way to format the code so that they either cannot select another cell until they have deselected the first one, or the first one unchecks itself and the new click ticks that cell?
Example: Person selects N8. Person then selects P8. Can it not allow P8 to be selected (and give a warning message) or can it uncheck N8 when P8 is checked?
View 6 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Jan 4, 2010
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
View 14 Replies
View Related
Mar 14, 2013
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then
Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X)
end if"
View 1 Replies
View Related
