I know this has been kicked around for a long time, but does anyone know of a way where the user cannot see or change the code. Either would be acceptable, but I would prefer where the user can not see the code.
View 2 Repliesi want to know is there anyway we can hide a row if the value of a particular cell is 0 ?
Like if a1 = 0 then Row A should be hidden?
I am using the code below on my spreadsheet. What is does is hides Row #1 until Row 41 is reached. When 41 is reached Row 1 appears. Unfortunately (for me) I need to alter this code and was wondering if anyone could tell me if it is possible. First the
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Rows(1).Hidden (Target.Row < 41) Then
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Rows(1).Hidden = Target.Row < 41
ActiveSheet.Protect
Application.ScreenUpdating = True
End If
End Sub
What I would now like to happen is that Row 1 stays hidden UNTIL Row 16 is OFF Screen. Can that be done? What has happened is that my row 16 contains the heads for my input table. I have now had to set the rows to resize to accommodate data input. If no rows are resized then Row 16 stays visible until I reach row 40. When row 41 is reached then Row 1 appears which contains my headers as well. I hope this makes sense to someone...
SO, IF I can get a code that would keep row one hidden until Row 16 is off screen then it will not matter what rows expand.
I have a file which contains many worksheets with many buttons on them for doing administrative work when updating data. I want to be able to click on a button on my "admin" worksheet to hide all the other buttons on the other worksheets. This is hide the buttons from the view of 'readers' so that they will not be tempted to click on a button to see what it might do. I have tried several different variation of coding, but can not seem to get the "right" object to work with so as to set the code to be xxxx.visible=false. I seen references to Shapes, Shape, Control Toolbox buttons objects, CommandButton, etc.
I would really appreciate if someone could give some pointers on how to designate in VB code, which will lend itself of future updates, the following:
in a toogle button, loop through the series of worksheets making each command button on that sheet set visible = false. Then for the other portion of the toogle repeat the process, but set visible = true.
I am new to programming in excel. For example user enters the value 1 in A1 and 2 in B2 The answer should be in 3 in C1. How to write this code ? Also is there any way where i can let the user copy the VALUE from the result (C1) into another Sheet in case if am using + Sign to obtain the result in C1 instead of writing a VBA Code.
Basically i am looking for the user to in put data in A and B and the result should come in C like wise i want the user to input the values in few rows and the user should get the result in the respetive rows under column C. The user should copy the values from C into another work sheet/ work book. The user should not have access to the formula but should be able to input values and copy the result. can i do this without vba code if so how ?
I need some VBA code to hide columns in spreadsheet when run. Basically, in row 7, I've entered HIDE in the columns that I want to hide when the macro runs (i.e. columns F,J,H,I,K, O,P,R).
View 7 Replies View RelatedBelow code isnt working for "5 Metre", columns dont hide from row 24 down.
My A10 is a drop down where i select 5 or 6 or 7 metre. If i select 5 it only hides rows 22 and 23.
I think the 2nd and 3rd lines marked red are stopping the rows from hiding!
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" Then
Rows("22:40").Hidden = (Target.Value = "5 Metre")
Rows("24:40").Hidden = (Target.Value = "6 Metre")
Rows("26:40").Hidden = (Target.Value = "7 Metre")
End If
End Sub
I need a code to hide and unhide 6 Active=X Listboxes on sheet 2.
I need to link this to a Active=X Commandbutton.Can the commandbutton caption say hide/Unhide listboxes when selected.
VBA code that can highlight all columns except ones highlighted?
View 1 Replies View RelatedThe below sheet sometimes returns blank (“”) cells in columns C:F. I’m looking for a code that hides the rows 113: to 117 when column C:F (all of them) are empty (“”) for that row. In the above example rows 115:117 should be hidden....
View 9 Replies View RelatedI have a sheet with 3 ranges of data in Sheet4, being partcode (B20:B20000), description (C20:C20000) and price (D20:D20000).
What I'd like is a macro to take a keyword entered in Sheet4!C17, search the whole range (B20:D20000), and hide the rows that don't contain the keyword.
Autofilter would be the logical method to my way of thinking, but can that work with a keyword entered in a cell, and can it all be hidden in a macro behind a control button?
I need to be able to save a copy of my spreadsheet but protect the structure so that the hidden rows cannot be opened by the person that I e-mail it to. At the moment I am using the current
Private Sub CommandButton2_Click()
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName
Call ProtectRobin
End Sub
Private Sub ProtectRobin()
ActiveWorkbook.Protect ("Robin")
End Sub
The saving part of the macro is working perfectly, however I cannot get the protection to work. It must have a password - I do not want the receipient to be able to go 'Tools' > 'Unprotect Sheet'.
I have a login page for my spreadsheet however a user can simply view the code to get the login username and password.
View 2 Replies View RelatedI have a very complex report with a variable numbers of rows in multiple sections. I am evaluating whether to display each row with a formula in the first column (those that I don't want to display have a "H" in the first column).
The following code works fine, but it takes forever. Ideally I wanted to trigger the code on the Worksheet_Activate() event but its so inefficient that I can't do that.
VB:
On Error Goto Errorhandling
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlManual
Application.StatusBar = "Processing..."
[Code] .....
with a simple code that could:
If column K2:k466 is blank or says "No" then columns L & M are hidden. If it says "Yes" it is not hidden.
Also,
If Column N2:N466 is blank or says "No" then Columns O,P,Q,R are hidden. If it says "Yes" they are not hidden.
Finally,
If Column V2:V466 has a zip code (or number) then columns T,U,W,X,Y,Z,AA,AB,AC are present. If there is no value in any of V2:V466 then they are hidden. .
Within a worksheet "Page_2" I would like VBA to perform following calculation: IF(COUNTIF(R:R;1);1;2)
Basically, look into column R of worksheet "Page_2" and look if there is in the column at least one number with the value 1, if there isn't show me value 2.
Depending on the value coming out of this function, VBA needs to hide worksheet "Page_3" if the value is 2 and unhide worksheet "Page_3" if the value is 1.
Here is the code which I'm using and that is not working.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.WorksheetFunction.CountIf(Range("R1:R5000"), 1) = 1 Then
Worksheets("Page_3").Visible = True
[Code]...
I am trying to create an input sheet with a specified number of rows. My plan was to hide all the empty rows in the table and ask how many rows were required in the table. A button would then be pressed to unhide all the relevant rows thus giving a table of the correct size. My best effort so far looks something like this:
Sub UnhideRows()
Dim i As Integer
Dim myRow As Integer
myRow = Range("A1") + 2
Application. ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction. Sum(Selection.Rows(i)) > myRow Then
Selection.Rows(i).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = True
End Sub
the cell to give the size of the table is A1 and the table starts in A3. The first cell in each row has an index number (=row() - 2), all other cells would be empty to begin with. My attempt did not work.
I have written a macro which references to several different sheets and cells therein. The macro is assigned to a command button on 'Sheet 1'. When I click the command button, the screen flickers and the user can see the macro running all the commands I have written. Is there a way of stopping this - maybe replacing it with a static screen view whilst the macro runs?
View 3 Replies View RelatedI am trying to run a macro that will hide rows when one cell is empty and another is not. Example: hide row when cell g is empty, but cell b is not. Or something to that effect. So far I've only used this code, but I would like to know how I can modify the code to fit the parameters I need:
Sub HideRows()
On Error Resume Next
With Range("B1:B300")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction. Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub
I'm trying to create a condition that hides rows when certain cells are equal to zero (column D + E + F). Anything else, I would like those rows to remain visible. Ideally, I'd like for this to be applied with the use of a macro (button) and then also deactivated when not desired (possibly another button). I've seen some varieties of code for this but none have worked flawlessly yet. An example sheet I would like for this to work on is my "ADM" sheet", among others.
View 2 Replies View Relatedi need a code to hide the sheets tab from the workbook. and only show the horizontal scroll bar and vertical scroll bar.
View 1 Replies View RelatedI have a worksheet in excel that I have hidden columns from AA right to the end. Which gives the user a nice worksheet with everything to the right of AA hidden in blue. What I would like to do is now centre the worksheet in the screen and have the same hidden blue effect to the Right of the worksheet in this blue.
View 1 Replies View RelatedI need a VBA code that will when used with a Form Control "Check Box" will unhide / hide a row. To be more exact, I'm needind the code to "Hide" row 34 when unchecked and "Unhide" the same row when checked. I'm using Excel 2010.
View 9 Replies View RelatedSo i don't have any events that i know of that should trigger my macros to run. But everytime i delete a cell, or hide rows, it seems all my code/macros just auto run. And i had a few times when i hid the rows, the code would start, and then my excel application would crash.
View 4 Replies View Related I have a workbook which contains 8 worksheets. I want to able to run a macro that looks for specific words in column A of each worksheet and hides any rows in between the specified words. The following code works except if a worksheet does not contain the specified words. Is there a better way to accomplish this?
Attached is an example of the spreadsheet. The code below works just fine on the tabs highlighted in green, but halts on the tab highlighted in red. The tab highlighted in yellow is showing you the rows I need to hide.
Example.xlsx‎
I have code which creates a tool bar which works Ok, however I can't get the statement correct to assign code to it.
View 8 Replies View RelatedI have a spreadsheet that calculates percentages and then outputs the results to a pie chart. There are 9 different percentages being graphed in cells A41 to A49. The chart looks weird if any of the percentages end up being 0, so I have the formula set to add 0.00001 to each calculation (so they show up as 0% and display on the chart as 0%, but truly are 0.00001). I would like it so that if any of these 9 percentages ends up being 0 (or really 0.00001) that the row automatically hides and thus won't display on the pie chart. How can I create a macro that automatically runs to accomplish this, and automatically updates as percentages are recalculated.
View 3 Replies View RelatedI have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas.
Is there a way I can encrypt the password within VBA so it's not visible to the other staff member?
Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.
Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.
I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.
I've used VBA to make a picture appear when all the correct answers have been entered and it all works well. However, not to be too mean to 15 year olds, many of my students can be devious little feckers, and I want to hide and password protect the VBA code so that they can't just change the pictures visible section to true. I can password protect the workbook and worksheet, but not the VBA.
I'm entering the Visual Basic editor and I can see my simple script. I then click Tools - VBproject properties - protection. I'm clicking the "Lock Project for Viewing" box then filling in the password and confirming the password and clicking OK. But I can still see and edit my script, despite protecting the sheet and workbook.
Code:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.