I want to be able to have a set of buttons or a dropdown menu where i can put all the titles of my columns in.
After clicking on one of the titles the column must hide or show itself, is there a way to do this?
It's for an automated sheet wich contains a lot of information and this will be used by a lot of people, i want to make it easy for people to simply generate a sheet with just the info they need.
I found this code for a button, so I can collapse and expand a set number of rows within that sheet. It works exactly the way i want it to, however, now I have a second sheet in my workbook, and I used the same button. Problem here is that when I activate the button, it opens the same rows in EVERY sheet in the workbook. "For each ws in Worksheets" so my question is what is the term for it to only work on a certain worksheet?
Code:
Private Sub ToggleButton1_Change()Application.ScreenUpdating = False
Dim ws As Worksheet
With ToggleButton1
If ToggleButton1.Value = True Then
For Each ws In Worksheets
[Code] ........
Other questions is about the ability to send an MS Outlook email from inside of excel.
I've been trying to find something that does the following:
Click button, Form pops up, has drop downs to select recipient, has field for subject, has field for message body, sends email.
I'm looking to make a simple button that would hide a given range of columns.
This is the simplest I could find:
VB:
Sub button1()
Columns("AD:AE").EntireColumn.Hidden = Not Columns("AD:AE").EntireColumn.Hidden
End Sub
Although this works nicely, there was another way to do it (looks more ergonomic and doesn't take up spreadsheet space). Here's a screenshot of what I mean: ColumnHide.gif
Running into this issue of "transworksheet macros"
I have a master "update" commandbutton on sheet 1. I have two sub "update" buttons on sheet 2 and 3 respectively.
What I would like to do is have the user click the master button, that would "click" the two sub buttons so they run their respective macros on their respective worksheets (within the same worksheet.
I have tried pasting the macro code in the master button, telling it to select the sub worksheet and then run the macro, but the marco runs itself on the master worksheet instead.
Need a macro for each button.
The sheet will be protected with a password (in the future, users will have varied access privileges).
Column A is designated as the "Button" Column.
There are 5 buttons here. Each representing the area on the sheet that needs to be viewed. Once the button is pressed, it takes you to that section of the sheet. At this time, I have designated each column area as:
a-z
aa-az
ba-bz
ca-cz
da-dz
I have tried this formula with opening tabs, but this won't work.
I am putting several buttons into my spread sheet and would prefer to use ActiveX controls rather than form controls. But I also need to hide the buttons from the end user (controls are to update data and will be used by the person who maintains the workbook). When I go to hide the columns, the ActiveX button doesn't hide with it. On the other hand, if I use a Form Control Button instead, it does indeed disappear when I hide the columns. Is there any way to get the ActiveX button to hide when hiding the columns?
View 9 Replies View RelatedI'm trying to record a macro which will hide and unhide columns K:P of data, but I only want one button. I know how to do this to produce one button for hiding and another for unhiding...but I want one combined button.
How to use vba, how I do this via the macro recorder?
I am trying to create a very large spreadsheet and i have got everything organized, but for easy viewing i want to have a toggle button that hides and un-hides certain sets of the columns (ie. Press down and it hides columns "B-F", "H-J", and "M-O".... then on depress it shows all the information again)
I have got some of the coding down, but am only able to get it to hide one set at a time, not all the sets. Can anyone help me out and let me know how i tell it to run multiple commands from the one press? here is the code i have already: ...
After scouring the entire OzGrid, I have found the following two macros that partly satisfy what I want to achieve. BTW, following two macros are executed on Sheet2 and I want to achieve the following:
On Worksheet_Activate, show columns that match the criteria value in Sheet1.D4 (Sheet1.D4 contains a dropdown list with about 6 text values). In addition to that, if Sheet1.D4 value is blank then show all columns. Right now, it successfully shows columns that match the D4 value but does not work if that D4 value is left blank, instead it hides all columns in range (C:CV). So in short, to the first macro, I need to add the criteria if D4 is blank, then keep the sheet intact and don’t hide anything, just show all columns.Once on that sheet after the Worksheet_Activate event has occurred, with the second macro “ToggleColumnsVisibility” following things need to happen. BTW, currently this macro is assigned to a button (from Forms Toolbar).If cell D4 has a value, then the button’s caption should read “Show All” and when that button is pressed, it should show all columns in the range (C:CV), and the button's caption needs to change to “Show Selected”. In “Show Selected” mode, when that button is pressed, it needs to show all the columns that match the D4 value.If cell D4 is left blank, then the button's caption should read "No Action" and when the button is pressed, it should not do anything but just show all columns.
As you will notice in the header, my experience with Excel is very limited, so detailed instructions will be greatly appreciated. I am also hoping that proposed solution uses some of the efficient/effective ways of using loops such as described below (example taken from this website from this link http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm)
Sub TrueOrFalseFaster()
Dim bYesNo As Boolean
Dim i As Integer
bYesNo = (i = 5)
MsgBox bYesNo
End Sub
Sub ToggleTrueOrFalseFaster()
Dim bYesNo As Boolean
bYesNo = Not bYesNo
MsgBox bYesNo
End Sub .........
what code I can put behind a button so that once I click on that button,
What ever cell i click on, the whole row of that cell will be colored a certain color.
Is this where i would used a target approach?
I currently am using a form button to create new rows.
I need two different kind of buttons. I have a button that does this-
VB:
Sub InsertRow()
'this inserts a row where the button is clicked.
Dim r As Range
[Code]....
However, when I try and create these codes using a Command button (I need it for the color), I get the error
"unable to get the buttons property of the Worksheet class"
P.S one other thing, say the button is in row 3, and Cells A1 and A2 are merged, when you click the button in row 3, it should extend the merge to A3.
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
Looking for a way to hide a given area of either rows or columns using a hide/unhide button command.
View 9 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
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 this workbook with 10 sheets. Each sheet has macros that are called from a floating toolbar. What I am trying to do:
I want to create a msgbox that warns me when I click in a toolbar's button to call a macro that not runs in the activesheet. Something like: "This code (or macro) doesn't match (or run) with the active sheet.
Is there a way to add a new sheet as soon as click on button on excel sheet with the name i give.
ex : there should be a button on the Index page , as soon as i click on button it should add a new sheet and i should be able to provide a name in the index page for that sheet.
I have many workbooks that I receive from clients I run various Excel VBA macros on depending on which workbook it is, and I need to automate all of this so that anyone can run an simple .exe user interface designed in VB 2005 Express Edition where when that interface is run, a window opens on the destop with a collection of buttons, when the user clicks one of the buttons, Excel launches, loads the appropriate file and runs an already-existing appropriate Excel macro, then some file maintenance is done, like moving a group of files from one subdirectory to another.
View 9 Replies View RelatedI am using VBA to pull a web query to get sone info from an intranet site.
Unfortunately, the data it pulls is usually old. I've found the only solution is to open a browser manually and to click the Reload button on the web page then navigate back to my workbook and run it again. Then it will pull the most up to date info.
I've tried several ways of forcing a refresh of the data, but have not figured it out yet.
I have been trying the past couple of hours to use the send keys button to tab down to the button on the page I need and to hit enter, thus forcing a refresh, but I can't seem to get that to work either.
On a spreadsheet I have a button which has a macro assigned to it, for some reason when I protect the worksheet then then click the button, nothing happens (or though I do get egg timer for a second). but when I unprotect the shet its fine.
ive used this type of method many tiems, and cant see what the problem is, have tried all combination of locking and unlocking cells in and around etc, but nothign changes it.
I have a spreadsheet with 6 buttons on it. Ive been using the code from Daves Download Page to log users of the spreadsheet, however im now want to log which button is being clicked the most. Is this possible? Ive tried to do it myself, by adding a hidden sheet, and adding code to each button to add a line to the new sheet each time the button was clicked etc, but faield miserably.
View 2 Replies View RelatedIs there any way, through VBA code, to click a button on a sheet in another workbook. This button has a macro assigned to it. What I need to be done is this :- the workbook which has the button is open. The code should click the button on a specific sheet. On this action, the macro assigned to the button should get called.
I could just call the macro which is assigned to the button in this code. But the requirement is that the button click operation should call the macro.
I have found that one can do quite a lot with the code, but this seems to be one a whole different level. I came across a possible solution in the below mentioned link but it doesnt seem to be working for me. [URL]
I need to capture / record a cross country race results in excel. Is the a code already available to log time (per click / press enter) as the runners complete the race and capture their time...?
View 1 Replies View RelatedI have the following code:
[Code]......
Each macro is assigned to its own button, Clear and Restore. What I'm trying to do is make it so that the macro attached to the Restore button will not activiate unless the Clear macro attached to the Clear button is clicked first and visa versa. I can't see to find anthing quite like it in my research.
I've been looking for a way to save a worksheet as a PDF document and place it in a folder in Documents.
View 4 Replies View RelatedI am looking at designing a simple radio button system which allows me to fill an adjacent cell with values from a certain column - so in the example, column C is the total value, and columns D-F will ideally have a radio button which a user can just click to select the value.
View 4 Replies View Relatedonce the command button on sheet 1 is clicked, a input box appears which asks "has this number been entered" and has a place to enter a number within the input box.
Once the info is entered in the input box, it searches through sheet 2 for the number. If the number is found, I would like for a box to pop up with the number found and the name that is next to the number from sheet 2.
If the number is not found, I would like for a box to pop up and say "number not found"
I have a User form which had a button which redirects me to a certain sheet, e.g. If I wanted to go to stock sheet I would click cmdViewStock and it would open the stock worksheet meaning it works, but the userform still stays open. Is there a way in which once I click the cmdViewStock it opens the Stock worksheet but closes the userform automatically straight after?
Here is the current code I have for the button:
Code:
Private Sub cmdViewStock_Click()
Sheets("Stock").Select
Sheets("Stock").Range("B4").Select
End Sub
I have a form that allows users to browse for a file so the location can be stored in a cell. When they select a file the form then creates another area so people can browse for another file. (Similar to an attach a file on an email)
The problem I have is that when I create the next button I don't know how to add a _click event to it.
I'he a button in Excel Userform and i want to open directory using button cliick and put selected directory in textbox1 e.g. i've selected D:Susheel then it will reflect in textbox1
View 3 Replies View Related
