Notes: To enable the Developer tab, follow these instructions: • In Excel 2010 and subsequent versions, click File > Options > Customize Ribbon, select the Developer check box, and click OK. • In Excel 2007, click the Microsoft Office button > Excel Options > Popular > Show Developer tab in the Ribbon. • To add a check box, click the Developer tab, click Insert, and under Form Controls, click. To add an option button, click the Developer tab, click Insert, and under Form Controls, click. ![]() Using Excel to create business forms means including elements available on your Web-based and paper forms, including check boxes. Like other Excel form elements. ![]() • Click in the cell where you want to add the check box or option button control. Note: The size of the option button inside the control and its distance from its associated text cannot be adjusted. • To format a control, right-click the control, and then click Format Control. • In the Format Control dialog box, on the Control tab, you can modify any of the available options: • Checked: Displays an option button that is selected. • Unchecked: Displays an option button that is cleared. • In the Cell link box, enter a cell reference that contains the current state of the option button. The linked cell returns the number of the selected option button in the group of options. Use the same linked cell for all options in a group. The first option button returns a 1, the second option button returns a 2, and so on. If you have two or more option groups on the same worksheet, use a different linked cell for each option group. Use the returned number in a formula to respond to the selected option. For example, a personnel form, with a Job type group box, contains two option buttons labeled Full-time and Part-time linked to cell C1. After a user selects one of the two options, the following formula in cell D1 evaluates to 'Full-time' if the first option button is selected or 'Part-time' if the second option button is selected. =IF(C1=1,'Full-time','Part-time') If you have three or more options to evaluate in the same group of options, you can use the or functions in a similar manner. Deleting a control • Right-click the control, and press DELETE. ![]() ![]() I apologize.I've searched this forum for an answer to this question. It seems there are a few posts, but nothing has helped. I need to enable a ComboBox (Combo 222) based on the selection of a CheckBox (Check 218) in Access 2003. I've tried the following: Private Sub ComboBox222_Change() If CheckBox218.Value = 1 Then ComboBox222.Enabled = True Else: ComboBox222.Enabled = False End If End Sub Nothing I try seems to work. My CheckBox and ComboBox both seem to work independently of each other no matter what I try. I imagine this is a fairly simple fix, but for some reason, it's not working for me. ![]() Thank you in advance for your help. Hi, This is a very basic question, but i have been struggling for hours, so I must ask. I I trying to simply fill a combobox on a userform from a named range on a sheet, I want to populate the combobox as soon as the userform starts. (I launch the userform from a button on the sheet) Where must I place the code? And will this code work? Code: Sub Button1_Click() Set UserForm1.combobox1.List = Sheets('Sheet1').Range('MyRange') UserForm1.Show End Sub I am very new to vba, so please excuse my ignorance, any help would be greatly appreciated regards jim. 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 trying to do a very simple copy and paste of a simple formula in Excel 2007 and nothing seems to be working. Excel will not let me copy and paste a formula and will only paste the value into to workbook. For instance, if I make a very simple spreadsheet such as: A1 type in 10, A2 type in =A1 (calculated A2 to be 10) B1 type in 5 And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work. All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem. Hi, Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too. I need to calculate only the Yes. I have tried using the filters in the Pivot Table field list and they are not working. So now i'm trying to add a COUNTIF formula in the Calculated Fields section. The formula I'm using is =Countif('Time limit extension'='yes') and i get the answer 'too few.' What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me. Please HELP!!! I automating a task at work that will save some time. Seems simple enough but for some reason the code I have to cause IE to wait unil ready doesnt seem to work in all cases. Im using: 'wait until the page loads Do While ie.Busy Or ie.readyState READYSTATE_COMPLETE DoEvents Loop This doesnt seem to work well enough, since actions are occuring that should NOT occur until the browser is ready. Any other ideas as to how to make IE wait until the page is completely ready? I wondered about possibly checking the.innerHTML to verify that a unique page element is present. Thanks, Scott. Hi Excel experts, I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value. Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet. Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for. Basically, I am attempting to construct a 'work in process' file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed. Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet. Once Status is updated to 'Complete', it would be helpful to be able to run a macro/click a button that would perform the transfer. Can anyone assist? Peace to you all I'm still new here but after I searched the forum I found great work done for the needy like me. So I'm so hopeful I'm concerned with making a timetable for my school which we used to do it manually I need a code to distribute each teacher timetable with the following basis: the number of classes_ the teacher is assinged to work with_multiplied by the number of periods ( which is changeable ). E.g; Teacher 1 has 2 classes (1-1 &1-2). Each class should be visited 9 timea a week. So Teacher 1 should work 18 periods a week the distribution I need 'd look like the example in Sheet('timetable') Please don't let me down any help 'd be greately appreciated. Hi folks, Hoping someone can help me quickly as I'm pulling my hair out and have a deadline getting too close. Been trailing the net and this board but can't work out how to do a simple variance. How do I work out the difference between two numbers - including negatives. If the numbers are all positive, it's fine as it's simply a case of A - B = C which is your difference. However, that doesn't work if B is negative. I need a formula that would give the following: First Number / Second Number / Difference 1 / 5 / 4 1 / -5 / -6 -1 / 5 / 6 -1 / -5 / -4 -5 / -1 / 4 Hopefully there's a simple function I've been missing. Thanks in advance, R. Hello, I'm pretty new at VBA and was wondering if you could help me out on this: I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? If yes, would you be kind enough to share the code? MS Access is not an option for me so I was wondering if you could help me do this in excel. Thank you so much and would really appreciate to hear from anyone soon. 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. I just worked with a data set where I needed to create a unique key based on existing information where no unique key was set up as a field. I accomplished it by concatenating (using the & [concatenation] operator) several fields based on what I hypothesized and observed about their content. I'm thinking there has to be a more automated way to discover the best unique key of existing fields and their data. I thought I'd post this as a challenge because I don't know the 'right' (or better) answer. Anyone have any ideas? I have a problem sometimes. I will click on a cell to add information. The cell turns blue and then wherever I move, it highlights those to. No matter where I go on the page. If I Alt-Tab and work in another program on my computer, that excel page keeps highlighting wherever I move even in those other programs (I know this sounds confusing). When I return to excel thousands and thousands of cells are blue. The biggest problem is that the highlighting won't turn off, no matter what. I can't select anything from the tool bars, do any work on the sheet or close the program. I can close it only with the task manager but when I open it again, the cursor is still stuck in the highlighting mode and won't perform any other functions. Do you think this is a problem with my computer, the excel program? I have changed my mouse and this didn't help. Is there some shortcut to turn off this highlight feature other than restarting my computer. Which is the only current way I can get rid of it. Thanks for any advice. 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. Hello, I am pretty new to excel programming/messing around so I'll refer to those who are much wiser than I am. I am trying to set up a form for work, in this form I want to have a page with a bunch of cells pre-sized. What I want to do is when I insert a picture from a job site, that when this picture gets inserted, it will automatically resize to fit within the cell. I don't have time to manually resize dozens of pictures and if I could get this to work it would be awesome! I tried to search for an answer using the search function, but I kept getting a fatal error about allowable memory? Thanks in advance for you help! I know this question has been asked a bajillion times, so I apologize for the redundancy. I am working with an Excel spreadsheet and saving it as a.csv file in order to upload to an application that parses out the.csv data as transactions. The system requires.csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a.csv. So if I do that, close the Excel window, and then open again (as the.csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as.csv, close the window and then open that file up again, that dang scientific format is back. Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to 'stick' so that they don't revert back to scientific format when I reopen the file? Thanks so much for your help! I have a textbox in my userform where anything typed in the box is entered into cell A2 in my worksheet. However, multiple lines of text are not properly displayed in my worksheet. I have enabled MultiLine and EnterKeyBehaviour in the properties window of the textbox so I can type multiple lines in my textbox. The problem is that when I press the button to enter the text from my txtbox to cell A2, the cell doesn't actually display the text in separate lines. It just places a square symbol in the place that enter should have been pressed and the text should have been split into separate lines. How can I get the separate lines of text in my textbox to be properly displayed in a cell? Thanks for your help in advance.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2018
Categories |