How can the answer be improved? A check box is a field which can be checked to store information. To create a check box in Excel VBA, execute the following steps. ![]() Hi guys, Looking for some help and would appreciate your help. I want to prevent people cutting/copy/pasting on a spreadsheet I have developed. A couple of users keep doing so which in turn knackers my formulas. There will be occasions where I will need to be able to use these functions for maintenance and updates. Ideally I would like have a private marcro which I can run to enable these features as and when needed. I've been at this all morning and feel like I'm going round in circles. I've tried various bits of code (sourced via google etc) & have ended up with a whole manner of outcomes but not the one I want. To summaraise what I am looking for is some vba code which will do the following Disable cut,copy and paste when sheet is opened Enable cut,copy and paste when closed Have a macro which when run will allow me to cut/copy paste so as to implement updates when necessary. Can anyone help? Thanks in advance. I am using the code below to disable the save function very successafully. However, is there a work around to allow a macro to save? ---------------------------------------------------------------------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'this disables the save function on the XLS MsgBox '****Save is Disabled****' ' Following line will prevent all saving Cancel = True ' Following line will prevent the Save As Dialog box from showing If SaveAsUI Then SaveAsUI = False End Sub. I have a sheet with a list of names on and I've noticed that when I use ctrl+F to use the find function, even when I know i have entered the correct name it still will not work. ![]() ![]() I have identifed times when I know the name is there on the sheet, but the find function says 'Excel cannot find the data you are searching for' Im unable to put any pics up as my work pc has java disable so photobucket ect won't work but has anyone else come across this problem and how did you rectify it? A search macro perhaps? I'm using Excel 2007 and often use filters to find inconsistent data (mis-spellings, etc.) and then clean up the data using the fill handle to fill in correctly formatted values. I'm finding that, with a simple filter on, dragging over cells hidden by the filter changes the values in the hidden cells too. This is something I don't remember from my last version of Excel. I'm wondering if I've mis-remembered how this control works, or if there is a setting I can't find. My goal is to drag and fill (or paste) in a colum with a range of rows wiht some hidden (filtered out) rows, and have the filtered out data be unaffected, if that makes sense. Today I ran into an odd problem. I typed in values for column A rows 1 through 10 then values for column B rows 1 through 10. Then in column C, I made the formula C1=A1/B1. The math was correct it showed 542 in the C1 cell. So I dragged that formula down and it showed 542 in all column C cells which is not correct. And when I went to check to see if the formula was correctly dragged it was. For instance, the formula in C2 is =A2/B2 however the value of that cell showed 542 which was not the correct math/value. But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from. This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening. Thanks for any tips on this. I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the _Click code to run the macros when the user clicked the buttons. All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name 'cmdButtonGetInfo' and '=EMBED('Forms.CommandButton.1',') in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work. It's like the buttons are being disabled when I close or open the file. Any suggestions? Hello, i've got the following problem: I want users to double-click on a row on a protected sheet and then do some code based on the row-number of the clicked cell. I've protected the sheet because it contains a lot of formula's. When a user double-clicks a row it triggers the code through the Workbook_SheetBeforeDoubleClick event. After the code is executed Excel shows a message that the cell that was clicked was protected etc etc. How can I prevent this message from popping up? I've already tried Code: application.displaywarnings = false but that didn't work Thanks. I have an Excel workbook that was created by a former coworker. It includes a macro that, among other things, displays a message box about the 2008 file. The macro runs as soon as the file is opened. I'd like to access that macro to correct the date to 2011 and see what else, if anything, it's doing for me (it doesn't appear to do much). I can find references to creating macros to hide and unhide rows/columns and I found ways to delete all macros in a workbook, but I cannot find anything about unhiding a macro without knowing its name. Does anyone know of a way to unhide this macro? Hello, Firstly i haven't used excel to a great extent since my college days. So i'm having to re-learn 99.9% of everything i once new. I am volunteering for a non profit organization and trying to create a reservation system for the rooms that they have - kind of like hotel software, but in excel (i did a similiar thing in college but for plane seats) Please find it attached. What i need it to do: Copy all of the info from the main page to the guest lit (a new row each time). From the guest list to the Gannt chart - i did a few tutorials on dynamic gantt charts using conditional formatting but cannot get them to work when based on data on a different worksheet. Finaly is there a way to check for availabilty on any given date? If just someone could point me in the right direction, i would be appreciate it so much words can't express!!! Best Regards, Jamie P.S for the calender drop down on the main page, i'm using 'microsoft time & date picker 6.0) - pop ups on this site - same file, better website. Hi there, I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys! In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2: =Sheet1!A3 (or whichever cell it is) That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. Without a fixed range, as new rows are going to be added to the column). If anyone could point me in the right direction, I'd be very very grateful. I am new to Excel and I am creating fillable forms in excel which are protected so that users canonly input data in certain cells. I am using Excel 2003. The users told me that they do not want text to go beyond the edge of the page when they are inputting data. They also want the words to wrap when they reach the end of the sentence. I am also using check boxes(yes/no) and Ifind it difficult to make the adjacent cells fillable only Thanks for your help and correct me if I am wrong as I am new to this board. I've been using Excel for years and had very few issues. However, I recently went into a spreadsheet to update it and was unable to select and enter data into an individual cell. When I click on a cell and try to enter #s nothing happens (my num lock is on). Then when I try to click into another cell it just highlights that cell, along with any other that I move my cursor over. Once I click on one cell I can't stop the highlighting from happening. I can't even click on anything in the toolbar. I am extremely confused, can anyone help? Hello - I am using Excel 2007. In prior versions of excel, when copying data, if I wanted to copy visible cells only, I would select 'Go To, Special, Visible Cells Only' and then copy a range a cells. The default was always set to copy everything (including hidden cells), unless I specifically selected copy visible cells only. In 2007, the default is somehow set to always copy only visible cells. Sometimes, I want to be able to copy all cells including those hidden but cannot seem to figure out how to swith this default option. I looked under Excel Options and did not see an option there. I don't want to have to unhide and rehide everything each time i copy. I know i could do the hiding and unhiding via VBA but would prefer not to have to. Thanks for your suggestions. I saw two threads in this forum that asked this question, with no good answer. I am posting this solution for anybody still struggling with this. The question: How can you prevent a cell's contents from overflowing into the next cell? Of course, you can make the column wider or turn on text wrapping, but you might not want to. Each of those solutions can mess up the layout of your sheet. You may just want to truncate the value. Some people have suggested putting a space in the next cell. This is unnecessary, a pain in the but, and will mess up any ISBLANK type formulas, among other things. The solution: Select the cells in question and turn on text wrapping (Format>Cells>Alignment>Wrap Text). Now select the row(s) in question and manually set the row height, by right clicking the row number and selecting 'Row Height'. Check the height of an adjacent row for a good value. Your cells will now not spill over either horizontally or vertically. They will simply truncate anything that doesn't fit. Be careful now, because parts of your data may be hidden. This can cause its own set of problems if one or two digits are neatly hidden away. Think ahead if other people might be using this sheet, and not be expecting to have some data hidden. Tested in Excel 2002.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2018
Categories |