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 RepliesI 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
I want to zero out or blank out my running balance in the columns where I do not have an amount entered. So instead of having my running balance total run all the way down the spreadsheet, I want it to be blank at the first cell where I have not entered in a new bill. I have attached an example of what I am trying to do.
View 2 Replies View RelatedI am trying to read data from an excel sheet and writing the data in a plain text file. To keep with the text file specifications, I have to write the data with two decimal places. Suppose that in A1 I have 12. In the text file, I have to write this value as 12.00. Similarly, if the value is 12.1, I have to write it as 12.10.
I tried to trick with VBA. Round, but no chance. Here is my code (assume that I am reading cont_weight from A1, and the value is 12):
Print #FileNum, Space(3) & VBA.Round(cont_weight, 2)
The text output is only 12. It *must* be 12.00, or else nothing will work.
I am trying to convert Degrees Minutes & Decimals of a minute (12° 34.567') to Degrees and decimals of a degree (12.57611°). I have the formula to convert latitude, that is two digits, but it doesn't want to work with longitudes, three digits. (see attachment). This is the formula that I am using:
=IF(A2="","",(INT((LEFT(A2,3)+MID(A2,4,6)/60)*100000)/100000)&"d")
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).
get my code to work.
I have a sheet with a dropdown box in cell "J1" which is meant as a 'Show only rows containing this value'.
Column Q, from row 3 downward has a pick box which lists the same values as the "J1" drop down box, but uses code to combine whats chosen with commas. An example of what is in a cell in column Q is "SeaHawk, BlackHawk, Squirrel, MRH", where cell J1 would be either "SeaHawk" or "MRH" or something completely different.
I need to only show rows, with text in column Q containing the smae text as J1.
My Excel>Preferences>View>Comments is set to Comment Indicator Only.
Working from keyboard and mouse, when I click on a cell holding a comment ($B$52) , the comment appears. When I click on a different cell the comment disappears.
When I run the code
...
Range("B52").Select
End Sub
the cell is selected, but no comment appears.
When I run
...
With Range("B52")
.Select
.Comment.Visible = True
End With
End Sub
the comment remains visible even after I click off of B52.
I get the same behaviour when I use Application.Goto rather than Select.
I would like to write a routine that
1) creates a comment for cell B52
2) selects B52
3) shows the comment in B52
< VB routine ends >
4) comment disappears when user clicks on different cell.
Does this require event code?
I have two options in a Cell as Applicable and Not Applicable. This is in a Drop-down menu and what I want is that when I choose Applicable, it should bring up additional 5 or 6 cells for me to complete. And if I choose Not Applicable, the additional cells should not show up. The default would be Not Applicable.
I bet you're getting sick of the "X" questions, and I have done an exhaustive search but the search filter kills searching for (X) or 'X' or "X" .. but nothing I did find is what I am looking to do. I have any # of userforms that when you click the "X" in the top right side, it will hide the userform... once.. Is there a fix to the code below to make it do it every time its clicked?
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
If CloseMode = 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
End Sub
Like I said, I don't want to disable it, I tried that off of one of the posts I found and thats just plain annoying, I just want it to hide the one userform and show the other. This will do it once and then you can click it all you want and it wont do anything.. see the annoying trait above.. this lasts until you exit the program.. so the X button can be hit repeatedly though out the day. The userform 15 is visible, userform 11 isn't shown, IE not behind userform 15.
how to creat a userform with a button (1) to show anouther userform with a button (2). you click the button on 1 and it shows 2. then when you click the button on 2 and it shows 1. I'm having a problem with the second part.
View 5 Replies View RelatedIs there a method of formatting specific cells to show zeros? Or indeed I could turn on show zeros in options and then hide the ones I don't want. Any tips / advice on how to selectively display (or hide) zeros appreciated.
View 3 Replies View RelatedIs it possible to hide the contents of a column that would only show once clicked on?
That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?
I have uploaded an example excel spreadsheet : example show hide.xls
I have a workbook that I want to show or hide a selection of cells depending on another cell when printing it.
Example
If cell x = A2A then display a certain cell set if anything else then exclude cell set from printing.
I've seen excel sheet view like this [URL].....
View 2 Replies View RelatedI'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:
[Code] ......
Why the above code does not respect the Master User "Jane" should be able to see all sheets?
Original source for this code was found here:
HTML Code:Â [URL]....
I'm creating an excel workbook that will allow any business or accounting student to go in and create their four year plan while giving them all of their requirements for their particular degree.
One of the features I wanted to have with this program is that it wouldn't display all the worksheets for each major, but rather the ones you choose that you are associated with. I was going to have a drop down menu on the first page that allows you to choose your majors, those being accounting, or business administration with its 5 focuses.
If I just allow all the worksheets to be shown, there would be 6 in total and I thought it might get confusing for a student who's only taking one of the 6. My hope is that on the first page, when you choose one of the options, it would automatically display the hidden worksheet that goes along with that major.
Basically, I am looking for a macro code to link to an 'Update' button that will do the following;
I will have 2 sheets, one sheet will be referencing data from other sheet with the following
=IF(Log Sheet!A4="Yes",(A1),"0")
This means, if it needs to be 'referred' it will go to the other sheet, and if it doesn't, then it wont and it will show up as 0.
Basically, every row will have this type of formula for 30 columns, and i would like a macro code that will hide values of 0, but even once hidden, will then show them again if in the other spreadsheet i change the value to yes, meaning i want the previously hidden row to show.
I'm trying to create a macro that will hide all the rows where the value in column E is equal to zero.
I'm currently using rows 1:700, but I may add to it.
I have looked through alot of this board and see the codes to hide all tabs old and new and code for each sheet.
My question is:
Is there a macro that can be used to do this as well. Say a button to hide all tabs and a button to show all tabs or does it only hide/show when the workbook is re-opened?
I have a spreadsheet that has been set up with totals at row 1010. The data that the spreadsheet contains only goes up to row 159. However data will continue to be added row by row over time. At the moment I have to keep hiding and unhiding rows to check the totals. Is there a way so that the spreadsheet will automatically hide all but five rows between the last row with any data in and the totals at the bottom of the spreadsheet?
View 9 Replies View RelatedI've been trying to make use of 'Run "doit", but the macro stops when it gets to my sheets called 'RST' and 'RST Pivot'. What I'm I doing wrong?
Sub DoIt()
Application. ScreenUpdating = True
With Sheet1.Shapes("Rectangle1")
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible)
End With
'Toggling sheets Forces Rectangle 1to show while code is running
Sheets("RST").Select
Sheets("RST Pivot").Select
End Sub
Also, do I need to change 'With Sheet1.Shapes' to reflect the actual sheet name?
I have a worksheet with 4 sections of rows. Each section I call Goal 1 - Goal 4. I want to show each section and hide the other sections by clicking on a button. I have used a column, P, to denote which Goal a row belongs to by entering 1, 2, 3 or 4 accordingly. This is the code I am using.
Private Sub ToggleButton1_Click()
With ToggleButton1
.Caption = "Goal 1"
End With
Dim rCell As Range
If ToggleButton1.Value = True Then
For Each rCell In Range("P2:P99")
rCell.EntireRow.Hidden = rCell > 1
Next rCell
3) Else
Range("P2:P99").EntireRow.Hidden = False
End If
End Sub
I have three questions -
1) This code works to show Goal 1 and by changing the >1 value to <4 I can make it work for Goal 4 - but I can't work out how to show the other Goals, 2 and 3.
2) Is this code an efficient way of doing what I want?
3) How can I make the "up/down" state of the toggle button actually relate to whether or not I am showing a particular goal? That is, if I click Goal 1 and then click Goal 4, I am showing Goal 4 but both buttons stay in the "down" state. I want the Goal 1 button to automatically return to it's "up" state when I click on another button.
code to write down to make a checkbox hide the text in a few cells..
What I want is..
When I tick the check box I want it to show me the text in the cells (C11:D11 to be specific) and then when I untick it I want it to hide the text so all you can see is the background colour.
Here is the attatchment of what I have so far:
I have a commandbutton that when clicked, it would make monthview1 visible. I can do this via the following
Private Sub CommandButton3_Click()
MonthView1.Visible = True
MonthView1.Value = Date
End Sub
However, I do not know what code to add so that when the same commandbutton is clicked, the monthview object would hide.
I need some code that will do the following in a UserForm:
If option1 is selected = textbox1 is visible, textbox2/combo1 is hidden
If option2 is selected = textbox2 is visible, textbox1/combo1 is hidden
If option3 is selected = combobox1 is visible, textbox1/textbox2 is hidden
I would like to be able to use the before save event to hide some sheets before the save then after unhide some sheets. So that the user carrys on with the sheets they had before saving but when the document is reopened the correct sheets are hidden.
This is what I have so far but unfortunately when you click close and then save changes it runs the before save code and then goes around in circles, reasking the user if they want to save changes
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Cancel = True
Application.EnableEvents = False
Sheets("Protected Content").Visible = True
For Each ws In Worksheets
If ws. Name <> "Protected Content" Then ws.Visible = False
Next ws
Me.Save
For Each ws In Worksheets
ws.Visible = True
Next ws
Sheets("Protected Content").Visible = False
Application.EnableEvents = True
End Sub
if it is possible to hide the contents of a column that would only show once clicked on?
That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?
I have a lengthy column containing text information. Within this column are various gaps. ie: several cells with no information.
eg:
..... Row F
1...text info
2...text info
3
4
5...text info
6...text info
7
8...text info
I would like to create a simple macro (switched via toggle switch) whereby it hides/unhides the rows containing cells with no text information.
It should also be mentioned that this column contains various background color formatting, for both empty and text cells.
I have a standard laid out spreadsheet, but in column C, D and E, there are comments in each cell which are filled with a lot of text. I would like to set a macro so that whenever an 'a' is in column A, the comments for that row are shown/hidden (the 'a' being a tick symbol in Webdings). So if I place an 'a' in A4 and A23, and click a show/hide button, it causes the comments in C4, D4, E4; C23, D23 and E23 to be displayed, and when it is clicked again (or it could be a separate button), they disappear. If no row has an 'a' in the A column, then the button has no action. I have recorded a Macro of me showing and hiding comments, but of course that is for a specific row.
View 6 Replies View Related