Is there a way (non vba preferred) to set up a button that will delete values in a set of 10 or so cells. I make hard fill updates to a tab each month and it would be more effecient if I didn't have to go through and wipe all the old information out.
View 2 RepliesJust recently My Excel 2010 has decided to not let me right click to format cells, delete or insert rows. I can do these functions from the ribbon, but not via right click. this happens in both existing spreadsheets where I am the author, or even a brand new spreadsheet like in the image below. we have restarted the computer, Uninstall and reinstalled Office and still get same symptoms. I got here thru google but cannot find an answer anywhere.
I am very computer literate and even our IT personnel have looked at this with no answer. as you can see in the image, these options are greyed out.
Running Office standard 2010, Windows 7 pro x64
I read one of the threads that talked about adding commands to the right click menu. I added a few commands to try out, but now I don't know how to delete them.
When right clicking on the sheet tab, I need to know if it is possible to remove the "Delete" from being an option. My workbook is structured to where if a single sheet is deleted, it screws up all my formulas. I tried to just password protect the workbook, but doing that removes the "Rename" function, which I still need.
View 9 Replies View RelatedI have a spreadsheet containing 100 "requests" which can be made by a user
i would like the option for the user to be able to delete a request should they wish it either by deleting a row or clearing the contents
Currently I have a single column which is full of buttons (one for each row)
when i click the button on row 10 I would like row 10 deleted
when i click the button on row 30 i would like row 30 to be deleted etc
is there a way to do this without writing 100 macros which are specific to each line?
I have a combobox which is manually scrolled through using the down arrow key. As the different combobox items are viewed, I would like to be able to delete the currently viewed item from the list if desired by clicking on a commandbutton.
The list items are contained in the range of B2:B500. Also, since this places a gap in the list, I would like to shift the remaining cells values in column B up to close the gap.
I have a worksheet in whichs Column A Cells , there is corresponding string content in Col. Z. Some cells of column Z contain of many lines (up to 100 line) which i want to reduce to one. I want to select one line through double clicking in the listbox of the userform and all other lines should be deleted and only the clicked one should remain. Code should then jump to the next non empty Z cell.
Columns B, C, D, E, F, G and H should also be shown in the userform and they should be live editable. I have attached and example file with userform and example data.
Locking text in cells but not the ability to change colour of cells
******** width="234" height="60" frameborder="0" marginwidth="0"
marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" id="aswift_0" name="aswift_0" style="left: 0px; position: absolute; top: 0px;">*********>
I have a spreadsheet where I can change the colour of a cell by clicking the mouse, I also have text in many of the cells.
What I need to do is protect (lock) the text so that no one can change the text in any of the cells, but I still want to be able to change the colour of the cells by clicking the mouse in that cell.
I wonder if it is possible to make a macro that right clicks a cell and then chooses a option from the list?
View 11 Replies View RelatedAny way to make it so that a cell can remain locked but be able to click a hyperlink? I need the cell to remained locked so nobody can alter the hyperlink name and value but I would like users to be able to click it and have it open in their browser.
View 9 Replies View RelatedI am looking to double click a cell in Column "A" to copy the ajacent cells to another sheet.
In worksheet "Summary". When I double click Cell A5, I want to copy Cells B5:D5 to Sheet "Timesheet", insert Row "5" and paste in Cells B5:D5.
I must be hitting a key by accident sometimes which makes Excel highlight the cells immediately below the ones I want as well. So, if I click in the middle of cell A1, it also highlights A2, A3 and sometimes A4 even though I haven't dragged down over them.
Why is this? I can't work out how to stop it doing it. Some previously recommended hitting F8 but this doesn't seem to stop it.
I have a spreadsheet that has several formulas that update once all the values are inputted however I noticed that if I drag the formulas down the column, the cells does not update until I click the save button.
View 2 Replies View RelatedI'm trying to write a code to say, pop a calendar if clicked on anyone of the following cells:
Sheet : "Friday"
Cell Range: D3 to D999, F3 to F999, H3 to H999
I'm hoping this function will not affect me from using the Sort (by date) later on.
I would like to enter N/A in the empty cells E6:E33 with the click of a button. Not sure how to write the code.
View 5 Replies View RelatedI have two columns in a spreadsheet.
In cell "$D$3" is the word "Period 1".
I want to be able to do the following.
Double click on cell D3, where the word period 1 is.
VBA then looks up that period in column B, and finds the corresponding weeks for period 1 from column A.
I then want VBA to list these weeks next to the period 1 I double clicked, so in column E.
I have attached a spreadsheet as an example.
I would like for a vba programmer to tell me if the following program can be written before I attempt to code it. When I open Excel I want to see cells containing names in a square array with red backgrounds. As I click on each cell I want the cell to turn green and I want an alphabetized column of the names to have a 1 recorded(after I click) indicating that the person is present. If a person's name is red then I need a 0 recorded. If possible, I would like to be able move over one column each day that I take attendance by clicking on a cell in a row above the column of names. I am a math professor and I write computer code most every day but I have only written some small routines in vba that did not involve events.
View 3 Replies View RelatedI am trying to set up a double click macro that checks the color index of the exact same cell you double click on, but in another sheet. What would be the syntax for that?
View 2 Replies View RelatedPublic Sub OptionButton1_click()
Sheet1.Select
ActiveSheet.OLEObjects("OptionButton1").Interior.Font.Bold = True
End Sub
The problem i have is when i click on one of the option buttons it should change the cells ive highlighted to bold.
I'm trying to double-click a cell to edit the contents, and I am off a few pixels, it treats the double-click as a double-click on the cell border, not the cell contents, and the focus jumps to another cell in the direction of the border instead of going into edit mode. The effect is as if I had pressed Ctrl and the arrow in the direction of the border I clicked on.
The only way I've found to disable this is to turn off the option "Enable fill handle and cell drag-and-drop", but unchecking that also disables actually useful things, like dragging the corner of a formula call to copy it down, etc.
Is there a good way to disable this cell jumping when I accidentally double-click a cell border?
I have an interesting question for everybody here. Let me see how to best explain it.
I start in the code making sure column Action Plan completion has a value in it. Copying in the issue modified column if neccesary.
'Back fill last modified
'I need to start with the first data row (3) and the action plan completion column
Let intcurrentrow = 3
Let intcurrentcol = intactionplancompletion
Do Until intcurrentrow > intmaxrow
If Cells(intcurrentrow, intcurrentcol).Value = "" Then
Let Cells(intcurrentrow, intcurrentcol).Value = Cells(intcurrentrow, intissuemodified).Value
'Need to ensure that everything is formatted as a date
Let Cells(intcurrentrow, intcurrentcol).NumberFormat = "m/d/yy h:mm AM/PM"
End If
Let intcurrentrow = intcurrentrow + 1
Loop
Later, I place this formula in a given cell.
=If(I9="Open-Remediated",M9-K9, If(I9="Closed-Remediated and Validated",M9-K9,"" ))
The info I copied above is placed into the column K.
When I run the macro, the cell formula outputs "#value".
However, if I go double-click then enter the cell calculates just fine.
I've tried excels overall recalculate function, but that doesn't change this.
Does anybody know how to simulate a double-click on a cell?
i want the user to be able to change the cell color on the click of a command button. the worksheet is protected. when the user click the command button the active cell changes to red and offsets by 1. then the work sheet is locked again.
The two problems I am having is 1. I want the range to begin from row 10, column k to column FD. all cells down
the second problem is the current code allows me to edit locked cell columns A to J ...
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.
Any way of deleting all rows not containing particular text? I did it as a loop but it took forever and I had to run it several times because it kept jumping over values. There must be a simpler way, I just can't think of it yet! still new to VBA.
Dim DateColumn As Range
Dim cell As Range
Set DateColumn = ActiveSheet.Range("a2:a623")
'remove unwanted rows (not = M XXXX MTD)
For Each cell In DateColumn
If Not cell.Value = "January 2013 MTD" Then
cell.EntireRow.delete
End If
Next cell
I would like to empty cells (not delete a row) that contains in column B values that are like Paper. In this cells are different materials (PVC, paper, steel, wood, glass) and also paper have different names (Paper 50g, Paper 80g, Paper 120g,...). I want that all that cells in column B that contains (Paper) are empty.
Before:
A
B
1
548787
Paper 80g
2
646411
Steel bar
3
634211
Steel pipe
4
521446
Paper 300g
5
521424
Paper 180g
6
...
After:
A
B
1
548787
2
646411
Steel bar
3
634211
Steel pipe
4
521446
5
521424
6
...
code that will find the last row of data, delete all cells, and move them to the left.
Please see below:
BEFORE
After
1
ABCD
Frank1
[Code]...
I have a workbook with parantheses in each cell. The contents are text and an example is;
Basketball-WNBA (71)
I want to delete the (71) from every cell. The numbers are NOT the same but will always be at the end with () around them. How can I do this without going to every cell? There are about 40,000 cells in the workbook..
I have used Vlookup to search and to return specific values to column 77. For those values that are not defined, Vlookup will return #N/A ( null value). Can I know how to write a macro that can go through each cell in column 77 and delete those cells that have null value? I tried the following code but it doesn't work
Sub clear_contents()
Dim rwindex, colindex
colindex = 77
For rwindex = 658 To 663
With Worksheets("Invoice").Cells(rwindex, colindex)
If .Value = Null Then
Worksheets("invoice").Cells(rwindex, colindex).ClearContents
End If
End With
Next rwindex
End Sub
I have manually highlighted a large magnitude of cells (I would have tried some sort of automation but there isn't really a pattern unfortunately). The cells that I did not highlight are useless to me, and I would like to get rid of them. Is there any way to delete all cells that are not highlighted on a given sheet?
View 2 Replies View RelatedI am trying to write some code that will compare three cells on a row and if they match then it will delete the row.
Column U-has Y, N entered
Column V-has Y, N entered
Column O-has 1, 2, entered (some cells may have the fill color set to red)
I need the macro to look at Column U and Column V and for example if the cell U2 has a N, and V2 has a Y then the macro would look at O2 and it there is a 1 with the fill color set to red the macro will delete that row.
The spreadsheet will vary on how many rows it contains. It can contain up to 5,000 rows and the macro would need to go through all the rows.