I am looking for a function to convert time given by my computer (Local time) in EST (Eastern Standard Time). We are several users of a same file (with timestamp macros) and all time need to be aligned to one time zone (EST), even if all users are working in different time zone (EST, CST and IST).
I need a mar that I want to attach to a button that would:Add a new comment with the date/time added to the username [format XYZ 22/10/09 14:48] and then I type the comment text, and/or Append a new comment to the existing comment, in the same format as for 1.
I have a macro that saves a sheet to another workbook. However, that sheet contains buttons with macros, so when saving to a new workbook, the macros get copied over.
Is there a VBA code to delete all of the buttons on a sheet? I can't name them individually, because for some reason, excel changes the name of the button between the sheet and the new workbook.
I need to have my macro select all buttons in the current row (including the button I clicked on and delete them. There are many rows with buttons. Each row needs to have this button. So I need it to be row specific.
I know that this is how to select the row that the button is in:
But I do not know how to select the buttons in that row. It needs to be included in this:
VB: Sub move() Dim shname As String, sh1 As Worksheet, lc As Long, rw As Long Set sh1 = ActiveSheet lc = sh1.Cells.Find("*", , , , 2, 2).Column rw = sh1.Buttons(Application.Caller).TopLeftCell.Row
[Code] .....
After many attempts and alot of research, I have some more code for this problem: This deletes all buttons in the sheet:
VB: Sub DeleteShapes() Dim shp As Shape Dim myVar As Shapes ActiveSheet.Activate
[Code] .....
And this deletes the current button only:
VB: Sub pressbuttons() With ActiveSheet.Buttons(Application.Caller) .Delete End With End Sub
Any way to either limit the first code to just a particular row, or expand the second one to the entire row.
I have inherited a spreadsheet that has a text box and a radio form that I cannot delete for the life of me. I can't click to select either one nor right click to do anything. I have an "Approve" and "Reject" button on top of them and those work great. But how do I delete the others?
I have inherited a spreadsheet that has a text box and a radio form that I cannot delete. I can't click to select either one nor right click to do anything. I have an "Approve" and "Reject" button on top of them and those work great. But how do I delete the text box and reject radio button?
So I'm trying my hand at creating VBA buttons and functions that do the following.
Button 1 : to add a column with every click. The button will create one extra column. So by design there will be at least 1 column. Example if the button is clicked twice it will create 3 columns. I just need a buffer col. (contains no data)
Button 2 : to delete empty columns leaving only one empty buffer column. That is this button will purge empty columns except the buffer column. OR if ALL the columns are filled, Button2 will create the buffer column.
Eg. I click Button 1 three times. It creates four columns. I only fill two columns. When Button 2 is clicked it will delete all the empty colums except the buffer column. OR I fill in all four columns, there is no buffer column, when I click Button2 it will insert the buffer col.
Button 3 : to add a row with every click. The button will create one extra row. So by design there will be at least 1 row. Example if the button is clicked twice it will create 3 rows. I just need a buffer row. (contains no data)
Button 4 : to delete empty row leaving only one empty buffer row. That is this button will purge empty row except the buffer row. OR if ALL the columns are filled, Button4 will create the buffer column.
Eg. I click Button 3 three times. It creates four rows. I only fill two rows. When Button 4 is clicked it will delete all the empty rows except the buffer rows. OR I fill in all four rows, there is no buffer rows, when I click Button4 it will insert the buffer row.
In the sample file there are six columns filled with data. The 7th col is empty(the buffer column). The column after that calculates the average of data in filled columns. There are eight rows with data and the ninth row is the empty buffer row.
The "add" buttons must be able copy the format/formula of the column/rows BEFORE the buffer colums/rows and insert it before the buffer column/row.
I have a code that copies a sheet into a new workbook. The copied sheet is for information only. On the original sheet I have various macro's assigned to buttons made from using Insert Shape command. I assume if I can find away to remove the buttons then the functionality of the macro's would be lost which is what I want?
I have created a button name "Add Power Source" on excel sheet that adds a table and a button(named "Add Row") to the sheet.Whenever the user clicks the "add power source" button a table and a "Add new row " button is created .This "Add new row" button is used to add rows to the table. Now suppose the user has pressed the "add power source" button 5 times,so that there will be five table on sheet and each having a "add new row " button associated with it.When the user wants to add a new row to the table 2 say .How will I come to know in which table the rows are to be added using VBA.
I am getting the following error when i am clicking a Button on sheet1 which is generating duplicate of sheet1.I am trying to delete the button when duplicate is created..Error is:" run-time error '1004' Unable to get the buttons property of the worksheet class" at this line in code"ActiveSheet.Buttons("CmdHistory").Delete"
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too.
I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have.
Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
I am trying to create a worksheet that has multiple radio buttons. Each radio button is linked to a Macro. I need there to be many buttons running down one side of the worksheet. Each button needs to perform a macro that is relevant to the cells in the same row that it is on.
Essentially what i am trying to do is make a macro that when the button is pressed copys data from H6 and paste it into B6. The button is situated above I6. I need a button for each row from 6 to 110.
While this macro is easy to create (i use the record button and then assign the macro to the button), i would have to do this 104 times and assign a new macro to each button.
Is it possible to change the color of buttons or command buttons? There does not seem to be any place that allows this under properties for buttons, although there does for command bars. However, I've tried recording a macro as I change the color, but nothing get's recorded so I'm not sure what the syntax would be.
I have a spreadsheet with several buttons and I'd like them to change colors as they are pressed so it's possible to see what you've already done. And then, as soon as any other cell on the sheet is changed, the buttons reset color.
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
I cant get my macro to find a set of data, skip that row and the next row. Any rows that dont contain my data I want to delete that row and the next row.
i have a slight problem i have this script which i want to run on all worksheets which are numbered (i.e. 1,2,3,4 etc) and to delete the rows in the F128 range which is under 00:05:00. I just cant figure it out to get it working.
Setting up a worksheet to monitor bookings within a time range of 06:30 to 09:30 then from 14:00 to 18:00. The data is collected in the following ranges. E3:E25 M3:M25 E29:E60.
I need the data collected from 06:30 to 09:30 - deleted JUST before 14:00 Then need the data deleted again JUST before 06:30 the next day.
Is there a formula I can use, where I can delete specific rows/columns each time? I have to download a large set of data each week and each time I have to spend hours deleting the same rows and columns. For example, if I have to delete Rows 1,2,3,4,5 and Columns J,I,H,G every time, how do I automate this?
I have a spreadsheet with date and time values of the format "dd/mm/yyyy hh:mm" in column A followed by some other data in cells of that row.
What I'd like to do is have a macro that will delete a complete row if the time value in column A lies between two times that I can specify in the macro (the dates are irrelvant)
I have a workbook of which has different times in columns D and E. What im trying to do is where the time difference between the columns is LESS than 5 mins I need that row deleting and cells shifting up.
I.E lets say D14 is 05.24 and E14 is 05.50. I need that line keeping but if E14 was lets say 05.27 then I need that line deleting and cells shifting up.
I need to be able to do this right down the workbook until the last entry.
i have a macro which is executing some cleaning up and then copying information from project sheets to a summary sheet. the individual project sheets are (currently still manually) copied/moved into the big(ger) overview workbook. to support the work with the individual project sheets they contain a macro that is assigned to some (three) shapes. one part of the cleanup is stripping the macros off the individual project sheets:
' remove the traffic-light macros of the project sheets from all sheets in case of unwanted or uncontrolled "infection" For CurrentWS = 1 To SheetCount Worksheets(CurrentWS).Select ActiveSheet.Shapes.SelectAll Selection.OnAction = "" Worksheets(CurrentWS). Range("A1").Select Next CurrentWS
on the line "ActiveSheet.Shapes.SelectAll" i get frequently (not always... ) a run time error '7': out of memory. it happens on at least two computers. i closed all other SW. according to the windows task manager information there should be plenty of memory free.
questions: - how to get rid of the error message? OR: - is there another (more smart) way to strip the ("imported") macros, while keeping the "main" marco alive that does not lead to an error message?
I have multiple sheet in excel.All the excel sheet has a table with formula.Now i want to delete all formula in my all excel sheet at one time without delete/loosing my values.
I have a UserForm which writes data to rows in a master spreadsheet. I'm attempting to write some vba code for a CommandButton in the master spreadsheet which can identify and delete duplicate entries based on "user ID", "Date", and "Time". I would like the CommandButton to retain the most recent entry from a user and delete all previous entries.
My master sheet is set out as such... A, B, C, D, UserID, Date, Time, Response
The users could potentially submit multiple entries on the same day. Ideally I would like to be able to click a CommandButton and delete each user's submission but retain their most recent one (based on "UserID", then "Date", then "Time").
I've searched all day for a solution and I've come close but I can not figure out a code that accounts for my three variables ("UserID", then "Date", then "Time").
I am working on a sales sheet for my business. I have a worksheet that has the names of everyone in my store that has sold anything in column A. I want to create a list that has just my full time sales people and will delete everyone else.
