I am working on a simple employee list with names, phone numbers, employee ID numbers, etc. I have created a VBA form to make new entries or change existing entries. The form has 'next', 'previous', and 'close' command buttons that work well, but I would like to have my data sort by employee name after any of the buttons are clicked.
My data is in worksheet "Employees" with employee names in column A beginning on row 2. Here is what I have entered for the 'Next' command button:
How can I return name of a command button on click? I want to create some sort buttons on a sheet I will use regularly to speed things up. I thought if I could call each sort button by the column letter the button sits in I can use this in a single sub for all buttons
e.g the button sitting in column A is called "A". when i click the button, it returns it's name to a variable which I can then use to sort column A. I know I could create a seperate routine for each button but I was just trying to think of something neater.
I have a sheet name "all" Sheet 15. What I want to do via VBA is sort range B4:I803 sort by Column G (ascending) then by Column H (ascending).
Have this sort activated by a command button or once the WB is saved then activate the sort prior to saving. The former is the preferred but if it's not to much trouble with coding then the former and latter solution would be great.
I have a 2 worksheets. One "Projects in Process"and the other "Project Completed". when the users fill in the information in cells A4-F4 (line 4 is the starting line and it could go down as far as line 23 or a23 - f23) In Projects in Process they press a "completed Project" command button and the data in a4-f4 copies over to worksheet "Projects completed". In the next available row (starting at row 4).
So right now I have the Private Sub CommandButton19_Click() Dim product As Double product = ActiveSheet.Range("J45").Value * ActiveSheet.Range("S44").Value Sheets("Sheet3").Range("R2").Value = product End Sub
I want to be able to switch the cell the product goes into based on a certain command button that I click(for example I have a command button for when I use copper wiring and one for aluminum wiring and I want when I click the copper wiring button for the product to show up in one cell, but when I click the aluminum button for the product to show up in a different cell , but on the same sheet). To be more clear I have a quantity command button(which is the code above) that i want to be able to recognize the difference between when the aluminum command button is clicked and when the copper command button is clicked.
I can accomplish what I want with the "Spin Button", but I'd really like to use the command button instead...
Can the command button be used for 2 functions?
Right now, I have a Spin Button, that performs this function: Private Sub SpinButton1_spinup() Range("E11").Formula = "=c11"
Is there a way to have the Command Button perform the same function? Not very familiar with VBA...I'd even prefer to go with the "Check Box"...but frankly, I simply don't know enough about the command functions to make it work.
I've a command button on one worksheet (named "Front") that when clicked it "resets" a large number of cells back to "."
I've ALSO a command button on another worksheet (named "Back") that when clicked it also "resets" a large number of cells back to "."
Both are embedded on their own VBA tabs.
Both work exactly as needed.
Is there a way I can move the coding from both to the "This Workbook" VBA tab, leave the one command button on the "Front" worksheet, and eliminate the one on the back?
I'm kinda guessing that this will require my rewording the "(Range("C5").Formula = ".")" (example line of code for the first cell it resets on the "front" tab) to something specifically referencing the "Front" worksheet, and the same for the ones on the "Back", but I could be wrong.
I have 5 xactive command buttons on a sheet1 and 1 xactive command button on sheet 2. If button1 on sheet 1 is clicked I would like to run the code named close1 from the click event of the commandbutton1 on sheet 2, but if button 2 on sheet 1 is clicked I would like to run the code close2 under the click event of sheet 2 commandbutton1 and so on. Is there some way of achieving the above
I have an excel spread sheet with say 20 or so sheets. Sheet 20 known as "Rates" is hidden. What I would like to do is when the user presses a command button say on sheet 1 he is asked for a password which will unhide the Rates sheet . If he gets it wrong a message pops up say incorrect please try again or have an exit button to cancel.
I have a macro which I use to do all sorts of routine things on a spreadsheet for my employees. (you guys helped me with that too. thanks)
I would like to add a command button to this sheet to run series of code and I want to add this to the final step of a macro.
Question, is there a series of code, which would 1. create the command button on the active sheet, and 2. add the required code to the command button, and 3. embed that button and code onto the active sheet all at once.
I need to do this regularly so it is a currently manual process I am trying to replace.
Ex. of code I want placed:
Rows("1:1").Select Selection.Font.Bold = True
This is not the exact code which I will use, but it will help me to understand what I would have to do to make it work.
I am building a code based on a command button in a row, which will check is a worksheet exists (message), if not create a worksheet from a template (from another worksheet specific to a on a cell value in the same row), rename the worksheet based on a cell value in the same row.
I am having some success for each task with exception to relative cell values .....
As the code will be specific to the row (one button per row) Questions:
how to i determine the location of the button that is clicked? (I assume once this is established i can use to pull values in the same sheet on certain columns....?)
Userform1 on the attached file has a "Calendar" button to display a calendar that allows the user to select a date and have it placed in the Date Text Box. It simply doesn't work unless you click on the userform area where the calendar should appear - but how would the user know that?
I have inherited support for the an Excel 'program' that seems to be fairly unstable. I am currently having problems with the following code in a subroutine (executed when the user presses a command button):
With ActiveSheet.PageSetup .PrintTitleRows = "$1:$7" .PrintTitleColumns = "" .PrintArea = ActiveSheet.UsedRange End With
Sometimes it works and sometimes I get the following error: Run-time error '1004' Unable to set the PrintArea property of the PageSetup class.
… on the .PrintArea = ActiveSheet.UsedRange
I am having a hard time figuring out what is causing its wishy-washiness. Running the following code (launched from worksheet named 'consumer' with a command button):
I have this macro that must sort my data from biggest number to smallest
Sub SortTopBottom(varASourceSheet As Variant, varSortKey As Variant, varSortRange As Variant, varCopyRange As Variant, varPasteSheet As Variant, varPasteRange As Variant) ', varAShourceRange As Variant, varSortRange As Variant) With Application .Calculation = xlCalculationManual .StatusBar = True .StatusBar = "Getting the top 10..." . ScreenUpdating = False End With varASourceSheet.Sort.SortFields.Clear varASourceSheet.Sort.SortFields.Add Key:= Range( _ varSortKey), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal............................................
For some reason it moves my headings from row 25 to row 2 in sheet2. Also - I read in Mr. Hawley VBA 10 commands that I should NOT select and should NOT activate. Is there a better way to do this sort? I use Office 2007 that does not handles some off the sort stuff I have found here.
This is probably really straight forward but cant see why it happens, the following macro works fine when called by a button created by the form toolbar but doesnt when called by a command button, get the runtime error 1004, "select method of range class failed"
I have read several articles saying using a command button as a toggle button can't be done but some articles have said it is possible using the state properties.
.State = msobuttondown .State = msobuttonup
Is there any way to combine this with the onaction property so that when you press the button, it stays down and activates a macro then when you press it again it pops back up and activates another macro.
I need to copy my worskheet multiple times to create about 10 of the same worksheets within a workbook. I know if you click in the very most top left hand corner of the worksheet, then click copy, go to the new worksheet and click paste, it copies the whole page, but it does not copy over the VBA Code.
Also, for some reason, it does copy over my macro buttons, but does not put some of them in the right spot?
I have searched this topic but I came up with nothing regarding my exact situation.
I have 5 buttons on sheet 1. I want them to all direct me to another sheet in the same workbook. However, some buttons will contain more than one sheet.
Example: Button 1 will direct me to 4 different sheets. Is this possible?
Also, When I open the workbook, I see many sheets that I am working from but I do not want them to show up when I open the workbook. The only way to see these sheets is to click on the command buttons at the top of sheet 1.