I have a shared workbook. Just used as a checklist for daily tasks. But opened on 5 pc's for users to edit. Is there a way to assign the process of click Review > Track Changes > Accept/Reject Changes... to a marco button?
View 1 Replies.370.375.376reject
.370.375.369reject
.370.375.372accept
I have 4 cells in a row as noted above. (I have listed the 3 examples of the results I need for clarification if needed) The first 2 cells are not variable and the 3rd cell is a variable number. The fourth cell is where I want a statement of either "accept: or "reject".
If cell #3 is less than cell #1 I want it to "reject", If cell #3 is greater than cell#2 I also want it to "reject" but if the number is eual to or between cell #2 and #3 I want the results to be "accept". So the purple numbers are fixed, the black numbers are variable and the red and green numbers are the results. So if I can get the "accept" and "reject" to work I would also like them to turn "green" and or "red" depending on the results.
I have a formatting macro that I recorded, nothing fancy just bringing in a few different spreasheets, and formatting them. I made this for someone who really doesn't know excel.
I linked this button to the macro and it works great, except at the bottom of the code I would like to put something that when this has run, it hides the button, so it can't be run again.
So I have a sheet with a lot of formulas and form control buttons that have been assigned macros.
When I go to protect certain cells and lock them and the activate sheet protection, suddenly my form control buttons with the assigned macros don't work.
I still need to protect some cells with formulas and don't want to leave the whole worksheet unprotected, but in order to allow the form control button macros to run, it appears that's what I have to do....or, is there another way to do this?
I have a workbook that contains 18 sheets, on 14 of the work sheets I have a comand button that runs the following
Private Sub CommandButton1_Click()
' Range("B3:B53").Select
' Selection.ClearContents
Dim nloop As Long
Range("C3:K53").Select
Selection.Copy
Range("B3:J53").Select
ActiveSheet.Paste
For nloop = 3 To 53
If nloop 3 And nloop 12 And nloop 15 And nloop 16 And nloop 23 And nloop 28 And nloop 41 And nloop 47 Then Range("K" & nloop).Value = 0
Next nloop
Range("B2").Value = DateAdd("d", 7, Range("B2").Value)
Range("L7").Select
End Sub
I want to put a comand button on sheet18 that will run the code so it does the same thing as pressing each command button on each sheet.
the sheets that I have the cmd on are:
Sheet1, Sheet2, Sheets3, Sheet4, Sheet5, Sheet6, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, Sheet15, Sheet16, Sheet17
I have a linked text box and spin button on a userform so the user can enter text or use the buttons and I'm having trouble barring non-integer inputs! Here's what I have so far:
Text box = NumberBox
Spin button= NumberSpin
I have a little trouble with my macros. I wrote several different macros, each for a special purpose. Then I created some Buttons, which would make it easy for others to work with them.
But I have now experienced several times, that my buttons don't keep their assigned macro.
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 RelatedI'm very new to Excel and definately to macros/vba. I've already received a tremendous amount of help with this file but I'm am really struggling here and getting very frustrated! I've got a pretty good handle on the excel part but the macros/vba is another story. Here is what i'm attempting to do: I've created a form "frmMacroButtons" which has 8 buttons. When the workbook is opened I want this form/window to launch. I want the user to be able to select any of the buttons but also be able to just click into the worksheet manually if wanted but have the window stay open to go back to use the buttons. Each of the buttons I've typed a description of what I want each of the buttons to do in the code view.
View 14 Replies View RelatedI accidently assigned a macro to an object. When right-clicking on the object, I am only able to "Assign Macro...", how do I remove it?
View 6 Replies View Relatedhow to alert and force second user to quit (i.e. even cannot open the file as "read-only) if the excel file is being opened by other?
View 3 Replies View RelatedI created an object using multiple smaller objects (shapes, text etc,) and grouped them to make an icon. I assigned a macro to the finished object. It works fine when the sheet is unprotected. However, when the sheet is protected, the macro will run only when certain parts of the object are clicked. The standard cursor turns into the "hand" when any part of the object is touched regardless of the protection status.
View 8 Replies View RelatedI've created a custom toolbar and populated it with a custom button. I have created a macro and it resides in ThisWorkbook. The macro is assigned to the custom button and the toolbar attached to the workbook. So far, so good. If I copy the workbook and rename it, when I click the button to run the macro it launches the original workbook and runs the macro. The macro effects changes on the renamed workbook copy. The macro name found in the assign macro dialogue starts with the original workbook name. The same effect if the macro resides in a module or sheet. Any known way to have the macro name change along with the renaming of the workbook such taht it refers to the currently opened workbook macros? Regards.
View 5 Replies View RelatedI'd like to write a macro to create buttons with the caption "Fix" in over 300 cells. Furthermore, I would like each button, when pressed to run a macro that would copy and paste the values (paste special) of the entire row in which the button is situated as well as copy and "paste special" the values in the fixed cells $J$2, $K$2,$L$2, and $M$2. I've attached a file to clarify what I'm sure is an extremely convaluted statement of my problem.
View 5 Replies View RelatedContext: I have a spreadsheet that contains a list of employee names and their certifications. I want to be able to assign a button to each employee in column B with a macro that is able to zip files from a folder that contains that employees name.
The following code assigns buttons to each employee in column B. At the moment the code I have is able to assign the macro "Zip" to each button.
[Code] .....
Now, this Zip macro is able to zip the file for the employee "Anthony Tran". However I need it to be able to recognise which employee's button I've clicked and search the same files as above except with that employees name instead of "Anthony Tran".
If it makes things easier, the code for creating buttons for each employee is able to name that button as the employees name that it represents.
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"
ActiveSheet.Select
Range("B4:B37").Select
Selection.ClearContents
Range("G4:G37").Select
Selection.ClearContents
Range("B1").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("F1").Select
Selection.ClearContents
Range("J1").Select
Selection.ClearContents
Range("M2:M3").Select
Selection.ClearContents
Range("B4").Select
ActiveWorkbook.Save
Application.Quit
Context: I have a spreadsheet that contains a list of employee names and their certifications. I want to be able to assign a button to each employee in column B with a macro that is able to zip files from a folder that contains that employees name.
The following code assigns buttons to each employee in column B. At the moment the code I have is able to assign the macro "Zip" to each button.
VB:
[COLOR=#333333]Dim Btn As Button[/COLOR]
[COLOR=#333333]Dim rng As Range[/COLOR]
[COLOR=#333333]For I = 2 To RowCount + 1[/COLOR]
[COLOR=#333333]With Worksheets("Sheet1")[/COLOR]
[COLOR=#333333]Set rng = .Range("B" & I)[/COLOR]
[COLOR=#333333]Set Btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)[/COLOR]
[Code]....
The following code is my Zip macro:
VB:
[COLOR=#333333]Sub Zip()[/COLOR]
[COLOR=#333333]Dim strDate As String, SavePath As String, sFName As String[/COLOR]
[COLOR=#333333]Dim oApp As Object, iCtr As Long, I As Integer[/COLOR]
[COLOR=#333333]Dim vArr, FileNameZip[/COLOR]
[COLOR=#333333]Dim FName() As Variant[/COLOR]
[Code]...
Now, this Zip macro is able to zip the file for the employee "Anthony Tran". However I need it to be able to recognise which employee's button I've clicked and search the same files as above except with that employees name instead of "Anthony Tran".
If it makes things easier, the code for creating buttons for each employee is able to name that button as the employees name that it represents.
if it was possible to limit data input to DDE (I am using winwedge to capture numeric data). Dave Hawley replied that all I had to do was lock the cells and protect the workbook. I have now done this but the data is not accepted unless the cells are not protected, anyone any ideas on what I am missing?
View 4 Replies View RelatedI have a cell tha't set up as a number format with the validation only accepting 3 whole number characters, however if the number is 098 then it converts it to 98. I need for that cell to be a number type format and to accept 0 as the first character. I thought I tried everything but I'm sure I'm missing something.
View 9 Replies View RelatedMy code is correct, I have confirmed this with several people. However, the code does nothing.
View 9 Replies View RelatedI have to validate a cell to only accept the letter a, d ,f ,g h,. I know this is probaly very simple but i havent touched excel since last november and have completely forgotton the most simple of things!
View 4 Replies View Relatedbelow code. I need to change this code to accept 12 digits.
View 13 Replies View RelatedI have a large spreadsheet with approx 1250 rows, the email address was filled in like this: Name (address@company.com)
the software that I am importing the sheet into will not accept the address in this format. I need just the address, no parentheses, name, etc.
Is there a rule I can run to delete everything I do not need so that I don't have to delete info in 1250+rows?
code which will allow me to update only todays date in particular cell in that cooumn and once the date has been entered it will save as value so that next day it will not change the date.
If I want to let cell accept date or word "NA" only if i use custom data validation.
View 3 Replies View RelatedWith the UDF below and the portion highlighted in red, how can the red part be made part of the arguments instead of specifying it in the code?
Code:
Function Countcolor(rColor As Range, rCountRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim Result
Application.Volatile
[Code]....
I am trying to set up a chart, such that its charted ranges can be change by changing some cell values.
I am having a hard time getting past first base, however.
I have created a named range:
Code:
Name: LowerTrend
Refers To: = Offset(Values!$AV$200,0,0,800,1)
When I try to edit the chart series by placing 'LowerTrend' into Series values it will select the range by showing the dashed line around it, but it will not chart the data and when I exit the 'Edit Series' dialogue and then come back, the named range is not shown.
i have a userform which has more than 20 textboxes. these boxes need to accept numbers only. what codes can i use so that all the textboxes will be formatted in such way?
My VBA form for excel currently only accepts numeric. I want two of its fields to accept alphanumerics. Those two textboxe fields are cboIns and TextCmt in the code below.
Code for the form is given below.
VB:
Dim dic As ObjectDim Ctrls
Dim myTBoxes() As clsTxtBoxes
[Code].....
I'm trying to create a formula where I can compare two cells but accept them as equal is one is plus/minus 0.1.
Example:
Cell A1 is 3, Cell A2 is 3.2, I want Cell A3 to turn red (or report "1")
Cell A1 is 3, Cell A2 is 3.1, I want Cell A3 to turn green (or report "0")