Show Sheet From Cell Value
Jan 22, 2009
I'm trying to show a sheet based on a value from a sheet.
Sheet1 cell k2 will have a month as digits "01".
I have added a command button to the sheet. This is the code attached to the command button:
Private Sub CommandButton1_Click()
Dim datepage As Range
Set datepage = Range("K2")
Sheets(datepage).Visible = True
Sheets(datepage).Select
End Sub
What it suppose to do is, if cell k2 contains "products", it will open up sheet "products". There are over 30 sheets that will be involved with this script. Just showing 1 example.
As it stands the script is not working, what could I be doing wrong here?
View 9 Replies
ADVERTISEMENT
Mar 20, 2009
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
View 9 Replies
View Related
Aug 5, 2008
I have a Workbook with multiple worksheets. On one of my worksheets (Sheet4) I have three cells, let's call call them A1, A2 and A3.
A1 = Annual Budget
A2 = Money carried over from last year
A3 = Current financial year.
Formulas in other sheets are dependent upon these cells. I also have 3 userforms, one relating to each cell, with each form containing a textbox which enters its contents into its respective cell after clicking a command button. The userforms should display if the cell it relates to is empty. For example,
Private Sub Worksheet_Calculate()
If .Range("A1) = "", Then
Budget_Form.show
End If
End Sub
The problem I have is that the userforms only appear if I manually change the contents of the cells that they relate to. I need the sub to run every time the workbook recalculates. On top of that, I want the sheet to be hidden but the subs to still run. After looking around on other sites, I have pieced together the following code and placed it in the code for Sheet4
Private Sub Worksheet_Calculate()..................
View 3 Replies
View Related
Mar 13, 2014
when I open my sheet on another computer, all cell value which contain formula show no value. just blank. formula is link to same workbook on different tab. when I save as again on my desktop, all value appears. excel 2007 is using.
View 3 Replies
View Related
Jan 30, 2014
Excel 2007.
I have a workbook with a sheet per day, each sheet has a variety of 'jobs' as columns and a variety of people and kit as rows. Hours are manually input each day during the month.
Simplified version... These are two separate sheets 'M060114' and 'Tu070114'.
Monday
Dig
Drive
Dave
4
5
Bill
2
7
Tuesday
Dig
Drive
Dave
2
7
Bill
8
1
I have a Pivot Table on a separate sheet. This was created using the Wizard (ALT +D +P).
Dig
Drive
Dave
6
12
Bill
10
8
So far, lovely.
But when I double-click on a number or right-click and choose 'Show Details' I don't get the sheet names in the newly created information sheet. I get the Row label, column label and a list of the entries.
So if I clicked on 10 above...
Row
Column
Value
Bill
Dig
2
Bill
Dig
8
That's fine with just a couple of entries but with about 80 columns and 250 rows spread across a month, therefore 30ish sheets, it's difficult marrying up the 'Show Details' sheet to the actual data.
So after all that, the question. Is there a way of getting the sheet name to appear in the 'Show Details' information sheet?
View 1 Replies
View Related
Jun 28, 2006
I have included the table. What I want to do is in a user form pick a county ie Devon, Then by clicking "go" all entries that contain "Devon" are then copied to a " sheet 2" I don't want to use the Ctrl-F
View 2 Replies
View Related
Oct 15, 2012
I have a sheet named SEND FORM that has some info that I would like for the user to see for about 10sec when he opens the work book and then it will hide and show the sheet they will be working in named STOCK CUTTING FORM.. Also is it possible to have the 10 sec timer to be shown in cell L12 in the SEND FORM sheet... I already have a couple of things in my open event which are below
Code:
Private Sub Workbook_Open()
Call RunOnTime
Call ShowMsg
End Sub
View 2 Replies
View Related
Oct 9, 2009
I have a column of data held in column B.
I am required to show the following.
If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0.
I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it.
do I need a macro?, or conditional formatting?
View 9 Replies
View Related
Mar 29, 2007
I am trying to write a code for a dropdown box where only the sheet selected from the dropdown is visible. Then, when you select another option from the dropdown, that sheet is the one visible (and all others are hidden).
View 9 Replies
View Related
Oct 9, 2008
I have a query Work sheet that has the names of my worksheets in the following format.. mmm dd, yyyy H15 has a formula that returns the sheetname as a value. What Id like to have is a checkmark that when checked it will take you to that worksheet. Ive played with it and came up with the following...but its not working...any advise??
Sub SHOW_H15()
'
Worksheets("Query").Range("H15").Text .Select
End Sub
Also id like to have the check mark named as "Would you like to see (h15) 's worksheet?"
One more thing...if a value is in H15 Id like the checkmark box to appear. If nothings in H15 dont show the checkmark Box.
View 9 Replies
View Related
Jun 11, 2008
I have used VBA and can do basic programming, but have no experience with popups beyond msgbox.
I've written a macro that I want to run, but it needs an input. This input is a worksheet name. What I want to do is run the macro, have a popup that has a combo box ask me to select which worksheet I want, either click ok and save that worksheet name to a variable or click cancel and exit the macro.
My Pseudo code looks like this:
Run Macro
Combo Box Popupoption1=sheet1option2=sheet2option3=sheet3If OK ThenSelection = Combo Box choiceElseIf Cancel ThenExit Sub
...Macro Code....
View 9 Replies
View Related
Jan 10, 2014
I have a some data in 1st column in which most of the data has decimals. For ex. 209.8, 224.4 like this. I have converted it by roundup function and get the exact result. I want to hide the decimals means not show decimals. Only the result will show like. 210, 224 etc. For your reference I have attached the sheet. I have manually typed this in the C column result area. That I need in A column.
View 9 Replies
View Related
Jul 14, 2014
I would like to use the combo box to perform a search function. I have 4 columns of data and 2 sheets, The first column in sheet 1 is the "Name". By selecting the person name in the combo box, the data of that person would be shown in another sheet (sheet 2). And now I have a combo box in sheet 2. How can I do this using the combo box?
View 6 Replies
View Related
Dec 9, 2006
See custom document properties for a give workbook by using File, Properties but where do sheet custom properties show up.
View 9 Replies
View Related
Jan 3, 2007
In A1 I have the time (military h:mm) 8:00
In B1 I have the 30 min braek we take at work 0.30
in C1 the end of shift time (16:00)
In D1 total hours (formula : = sum(C1-A1)-B1
and it is OK EXCEPT if the employee has the day off.
In that case A1 and C1 are empty but because of B1 I get in D1 the terrible ####
How can I remedy this ... this sheet cannot be edited in anyway. ( I cannot delete or edit anything)
Thought of an IF but the #### are not a value so I cannot do much in the way of conditional fprmats ... how not to show these #### when the sheet is printed.
View 9 Replies
View Related
Sep 14, 2009
I have a workbook with about 70 sheets, some are specific for data entry, others for data review.
I share the workbook with my data entry team, and lock the review sheets so that no one can modify the data on them or change the formating. End users review the final protected sheets for the information that is appropriate to them.
I need to track the status of the protection of each sheet, such that I can edit the sheets as needed, then protect / lock them once I am done.
Is there a way to indicate, in a cell within the indvidual sheets, the status of protection for that sheet?
For example, if the sheet is protected, the Cell would read "LOCKED" and if the sheet is not protected, the cell would read "EDIT".
View 9 Replies
View Related
May 12, 2007
when i open a xls, i would like to Display the Vertical Menu Of Worksheets (at the bottom left of the tab names)
--this worksheet has many sheets and i find this an easy way to navigate...
(also, how can i write a macro--for a user shortcut--to display this--currently, i hover my mouse pointer over the bottom left tab navigation object and right click to bring up the vertical display of sheet names...)
View 9 Replies
View Related
Apr 1, 2008
I have 5 sheets in a workbook named the following:
Monday delivery
Tuesday delivery
Wednesday delivery
Thursday delivery
Friday delivery
I want a macro to fire when the workbook opens. I want it to look at what day it is and then show the sheet for the next day and hide all the rest.
Example, with today being Tueday, if someone opened the workbook, I want only the Wednesday delivery sheet to be visible and all the others to be hidden, well very hidden.
Then tomorrow it will show Thursday delivery and so on. If opened on a Friday, it should show the Monday delivery sheet.
View 8 Replies
View Related
Apr 21, 2009
I would like to be able to format a worksheet, to show cells which have a formula in a different color and those with a value.
View 3 Replies
View Related
May 8, 2009
formular to show a coinage total in a spread sheet im sure its to do with < > but new to this and dont have a clue any help apreciated. This is what i am trying to achieve.
excel formula If A1= 10 Then B2= 0
If A1 =7 Then B2 = -3
If A1=13 Then B2= 3
View 13 Replies
View Related
Aug 28, 2009
Since I'm making a PivotTable with my macro, when it runs I always get this...
‘The PivotTable report will not fit on the sheet. Do you want to show as much as possible?’
I always choose yes, because in the end the user isn't going to use all of the data, they will be narrowing it down. Anyway, this comes up a few times througout the macro. Is there something I can put in the code that, if pop up box, always choose yes?
View 2 Replies
View Related
Apr 1, 2009
I need to display a value that is in a table on one sheet when the month is matched on the first sheet.
The month that I need to match up is on sheet1 in cell B1
I have a table on sheet2 that is a year budget, the cell range for the months are C5:Y5
Under each month I have a list of headings and it is these cells that I need to display on sheet1 in the relevant box.
View 9 Replies
View Related
May 11, 2006
I have a sheet that the is preset with the autofiter on. The sheet is protective. I have the following code as a marco and all works fine but if alldata is not already showing. If all of the data is showing and I run the marco, I get and error. What do I need so if all the data is already showing, one will not get an error?
Also from my code the marco is set to only run in one sheet or if the user is another sheet will the marco run?? I am guessing that the marco will run in any active sheet. If that is so what would I need to change to have the macro run in only one name sheet, for example sheet named "Frog"?
Sub AllData()
'
' AllData Macro
' Shows All Data
'
' Keyboard Shortcut: Ctrl+a
'
ActiveSheet.Unprotect Password:="pword"
Worksheets("Diag List").ShowAllData
ActiveSheet.Protect Password:="pword"
View 5 Replies
View Related
May 1, 2007
I have autofilter enabled on the header row across the top of my worksheet. Is there a quick line of VBA I can use to toggle EACH one to (ALL). This would act as a "Reset" in my spreadsheet and display all content. I had a solution that worked, until I made the workbook shared / password protected.
View 3 Replies
View Related
Mar 31, 2014
Is there any vba codes that can show tab/sheet that is based on the filter selection.
For example:
Cell A1 have a data validation of Apple, orange and Mango
I have 3 tabs named Apple, orange and Mango.
So every time I select Apple on the main in page filter. Apple tab will show. and if orange then orange tab will show.
View 3 Replies
View Related
Jan 6, 2009
Having problems with the following:
First Column Second Column Cumulative (B-A)
1 10 9
2 20 27
3 30 54
4 40 90
Every day,a new row is added and i want a formula at the top of the sheet to show the latest figure in column C.
View 2 Replies
View Related
Feb 23, 2012
I have a workbook with 3 sheets.
Sheet 1 is a sheet that has a shape with macro assigned to show Userform6.
Sheet 2 contains data that is displayed on Userform6. I use Userform6 to show statistical data that is on Sheet2.
Sheet 3 contains data that is input to ComboBoxes on Userform6 using the RowSource Property.
On Sheet 2, when I select Userform6, the data is correctly shown in all comboboxes and Textboxes......
On Sheet 1 when I select Userform6, only the comboboxes show the data...the textboxes do not show any data.
I do not want users to see the data on Sheet 2 which is why I want to show the Userform on a different sheet as the data sheet will be hidden from users.
Question is: How can I show Userform6 on Sheet 1 with all the data showing in textboxes?
View 9 Replies
View Related
May 31, 2013
i want to know how to show the progress of code exceuted in an excel sheet. like when we download a file from web or server it shows the staus of the file transfer.
View 2 Replies
View Related
Jun 26, 2014
formula to use in my report
Let's say I have a summary table on Sheet 1 6000 rows of data on sheet 2.
In Sheet 2 I have 3 columns, Name, Month and Score. Name Column (A) has names of students Month Column (B) has months January, February and so on. Score Column (C) has data from Green, Yellow and Red. Red is if they failed.
In the summary table on Sheet 1, I need to show all the names that got a "Red" Score. And their corresponding months.
It should look like this.
Month l Name l Score
-----------------------------------------
January l Mike J l Red
-----------------------------------------
March l Joe M l Red
View 2 Replies
View Related
Nov 5, 2006
compare data from different worksheets
For clarity, I have placed the sample datasets at the following URL
[url]
Given: the datasets for sheet1 and 2 might not contain the same number of rows.
I am looking for new rows (it might be inserted in any position of the rows) in sheet1. The new rows will be highlighted in red color. For example:
[url]
View 9 Replies
View Related
