Code Breaks Sometimes/sometimes Not: Subroutine Executed When The User Presses A Command Button
Mar 10, 2007
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):
Sub subConsumerFacesheet()...................
View 3 Replies
ADVERTISEMENT
Apr 19, 2006
i have this code which askes the user for a job number once the workbook is opened.
Private Sub Workbook_open()
' If sheet was named by original open routine, exit
If ActiveSheet.Name = "Main Roof" Then Exit Sub
' otherwise
Do
Returnvalue = InputBox("Please Enter a New Job Number.", "Information")
' Allow changes by entering q as the Job Number
If Returnvalue = "q" Then Exit Sub
' Delete the ' from the front of the following two lines and
' then when you enter q as the Job Number you will also be
' asked for a password. The default password is toe.
what iam trying to do is get it to open the userform "WorkSelection" after it has completed the above code.
View 3 Replies
View Related
Mar 2, 2007
I'm having trouble calling a subroutine from a command button. It's puzzling because I've set up buttons before and didn't have this trouble.
Here is my button
Private Sub EPConversionButton1_Click(ByVal target As Range)
Convert_Hrs_EP target
End Sub
And here is the subroutine.
Sub Convert_Hrs_EP(target As Range)
End Sub
There's nothing there yet, but I keep getting error messages regarding the transfer from the button code to the subroutine.
The message is: Procedure declaration does not match description of event or procedure having the same name. The Help file says this means that my procedure has the same name as an event, but does not have the same signature. But it's not so.
View 9 Replies
View Related
Apr 14, 2009
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies
View Related
Jan 29, 2009
so I know how to change the color of a command button(right click, properties), but is there a way to change the color to a specific red green blue range? lets say 102 28 02 for example
View 2 Replies
View Related
Sep 22, 2006
Is it possible to have a user form command button perform link or steps that are recorded with macro recorder? I am trying to reformat cut and paste a xls raw data file. I am trying to keep my file uploads to a min., but can upload an example if necessary.
View 4 Replies
View Related
Feb 27, 2013
I'm having a problem creating a Reset All command button that will remove user entered unlocked cell values from an entire workbook save for one specific worksheet. So for example, I have five tabs labeled as "DTF", "Week 1", "Week 2", "Week 3" and "Week 4". I want one single command button (placed in the "DTF" worksheet) to remove all of the unlocked cell values from "Week 1" to "Week 4".
View 9 Replies
View Related
Nov 14, 2011
I am creating a userform in Excel 2007 which requires a user to pick their name from a drop down box then press Ok, what i want to do is disable the Ok button until the user field has been selected.
View 1 Replies
View Related
Apr 29, 2008
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).
View 13 Replies
View Related
Feb 25, 2009
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.
View 9 Replies
View Related
Feb 6, 2009
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
View 4 Replies
View Related
Apr 30, 2013
I have placed a CommandButton1 and CommandButton2 on the Sheet1....how do i then assign the macro code to these Buttons
CommandButton1 is called MANAGERS GROUPS (macro called Split Sheets)
CommandButton2 is called CLEAR MANAGERS GROUPS (macro called clear_managers)
It was working before as i just used rectangle shapes as the buttons but have since removed these shapes and used the CommandButtons
The error is below in RED
Code:
Option Explicit
Public TrainingRows
Sub SplitSheets()
'
Dim rCell As Range
Dim sht As Worksheet
Dim lastrow As Integer
[code].....
View 7 Replies
View Related
Sep 6, 2013
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.
View 9 Replies
View Related
May 11, 2007
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.
View 9 Replies
View Related
Aug 16, 2014
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....?)
View 2 Replies
View Related
Oct 7, 2008
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:
View 5 Replies
View Related
Oct 1, 2006
how to change in a for next loop the CommandButton.caption in a usersform
For n=1 To 300
CommandButton & n.caption= Sheets("info"). cells(n,1)
Next n
View 9 Replies
View Related
Mar 21, 2006
I have a Form that user fill in information on, once they click the OK button, excel is filled with the inserted text on the form in the correct cells.
My question is, how do I validate that they have actually entered data in some cells, which I want to make mandatory, and if they have not, prompt them (this could be a simple message box) to fill it in. Let them fill it, and once they click OK again, check again... until all the mandatory fields are filled, only then will the macro fill in the excel cells.
View 9 Replies
View Related
Jun 10, 2013
I was thinking of how the Worksheet_Change(ByVal Target As Range) macro can be used to paste values instead of formula when the user uses Ctrl-C and Ctrl-V. Instead of assigning a macro to a command button for user to activate.
Suppose I have a worksheet where the range ("D7:D56") is where I would want the user to paste his values in it.
I have to factor in the possibility that these values have formulas attached to them, and that the user is not tech-savvy enough to know about the "paste special --> values" functionality of excel, and chooses to use the Ctrl-C, Ctrl-V method instead.
What then appears are ####### which might alarm the user.
Neither would we want the user to press a button (which we can assign the xlpastevalues macro as an alternative) as that adds an extra step for the user. There is also a chance the user might not use the button, or overlooked it.
Is it possible to use the Worksheet_Change(ByVal Target As Range) macro, or sth similar, to paste values instead of formula when user uses Ctrl-C and Ctrl-V?
View 6 Replies
View Related
Jun 15, 2014
well, the clear worked well and now I have another idea for a button for my worksheet;
I want the button to insert values from other cells. so when it is clicked, the values in cells F82-F86 are entered in cells E19 - E23.
View 5 Replies
View Related
Feb 26, 2014
I need to allocate an existing subroutine (its code below) to a new button I'd like to add to my sheet. Two questions arise: to use "form control" or "activeX"?, also should I redfine the first line of the code?
[Code] .........
View 4 Replies
View Related
Mar 22, 2008
I have some code which formats a worksheet based on user selections in a form. The code works, except when the user has made the same selections in two multi-select list boxes. I need to find a way to determine whether the code has already formatted the worksheet.
To me, it seems that there might be two ways to do this, 1) have a test on the original block of code that checks whether this portion of the code has already executed for this variable or 2) check the worksheet for the string (the code formats the same string each time but adds the variable for the current iteration of the loop only if Year1 = True.) The problem is that I only want to format the column if it hasn't already been formatted for that selection.
If CostShare = True Then
For Each ctrl In UserForm2.Controls
If TypeName(ctrl) = " ComboBox" And ctrl.Visible = True Then
If ctrl.Value = "Cost-Share" Then
t = ctrl.Tag
For Each ctrl2 In UserForm2.Controls
If TypeName(ctrl2) = "ListBox" Then
If ctrl2.Tag = t Then
With ctrl2
For ii = 0 To .ListCount - 1...............................
View 2 Replies
View Related
May 11, 2006
write a vb subroutine that accepts a date from the user and then displays a summary of the data (which i have) for that day in a message box
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
Oct 22, 2009
When I close my worksheet excel asks me if I wish to save the file one last time, if I click 'yes' I would like some more code to be executed and then have the spreadsheet save itself and close definitively, how can this be done using vba?
View 9 Replies
View Related
Jul 8, 2014
I was working on a presentation for work where I wanted to build a 'Family Feud' type board to play a game. In the board I am using activex text boxes with code that would hide the text box to reveal the answer underneath or it would show a custom shape (an X in a box) and play the buzzer sound. The code is pretty simple, first I would make the shape visible, then I would play the sound then the shape would be made invisible. If I step through the macro everything works fine, but when I run the macro, you never see the shape. It's almost as if the sound plays before the shape shows up and then it is made invisible again. I tried putting a wait and a sleep command between making the shape visible and playing the sound but that made no difference. If I remove the code to hide the shape at the end and run the macro, the sound plays and then the shape appears. Is there anyway to have the shape appear prior to or at the same time as the sound plays?
Code:
Private Sub CommandButton8_Click()
Application.ScreenUpdating = True
ActiveSheet.Shapes("First Strike").Visible = True
Play_Strike_Sound
'The previous line refers to another macro that has the code commented below. The sndPlaySound32
'function is one I picked up from cpearson.com
'sndPlaySound32 "C:\_Fin SysSoundsff-strike.wav", SND_SYNC
ActiveSheet.Shapes("First Strike").Visible = False
End Sub
View 3 Replies
View Related
Dec 14, 2013
VBA jumps out of my called subroutine which is stored in a Module halfway.
When I call the SortLec() sub, it runs until y1 = Application.Match(MC & "-" & CI, rng, 0), and then it just jumps back to the SUB commandbutton1_click() IF statement. Why is it that VBA skips the rest of the code from my sub?
Option Explicit
Private Sub CommandButton1_Click()
Dim i As Integer
Dim numofrows As Integer
Dim workbook_directory As String
Dim file_name1 As String
[Code] ............
View 2 Replies
View Related
Feb 3, 2010
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.
View 2 Replies
View Related
Aug 31, 2007
I have a userform with a button on it named But1. Is it possible to store But1 into a variable then activate the But1_Click action using a variable?
Dim strButname As String
strButname ="But1"
Now, how would you activate the But1_Click method using strButname variable. With listboxes you can use the Control(strListboxName) methodology?
This doesn't work, but gives you an idea of what I am trying to do.
Controls(strButname)_click
View 9 Replies
View Related
Nov 8, 2012
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.
View 4 Replies
View Related
