I'm looking to perform a Find and Replace over all text boxes on all sheets.
I found this vba over at excel ribbon.tips which does the trick, but only works on the active sheet... I'm looking for it to work over all sheets. I'd also like it to look for 2 different instances.. For example, Find A replace B, and Find C replace with D.
I have a large workbook, with multiple sheets. I would like to be able to select a name from a drop down menu, and have the cell color change for all entries of that name in my other sheets. I can do it manually, but it would be much cleaner and easier to have Excel do it for me. I have looked at other answers here on this forum and know that if it can be done,
Last weeks I've had too few spare moments to answer questions, hope to pick that up soon. I have a strange problem now with some checkboxes on a multipage control.
Please see the attachment.
I want to check all the 5 boxes on a page if the box above is checked, and vice versa, uncheck them all if the box above is unchecked. I use code of Dave Hawley to loop through the checkboxes.
This doesn't work, but oddly enough, it works if I insert a MsgBox in the code.
I need to link two text boxes on two different sheets. Is there a excel formula or macro to do this. I am entering text in textbox 1 on sheet1 and same text needs to show in textbox2 on sheet2.
Moving between different sheets changes the procedure window, which is a good thing, but can also can be error prone and annoying to search through several windows searching for the procedure or module currently working on. Is there a setting somewhere to prevent VBE from changing the current procedure window?
I have an excel file containing three sheets(1,2,3). And each sheet has 9 Columns (A,B,..,I). Now for the first sheet i have a functioning mask (userform) through which i can edit the columns live and add new rows.
Now i have a userform which is devided into three parts. The third and the last part is working fine which is linked to Sheet 1. Now i want the first part(on the top) of the form to be linked to Sheet 2 and the midpart to sheet 3.
In first and second part of the userform there is button (copy this to sheet 1 as new). This button should be coded in a way that if its clicked then the active entry (in sheet 2 or in Sheet 3) should be pasted at the end of the sheet 1 rows and the textboxes linked to sheet 1 should jump therer so that the new entry can be edited directrly in sheet 1.
The mask can be opened by the button in Sheet1 column A1! I am using 1280x1024 pixels for my grafikcard.
I am attaching two files. Excel file 2003 xls in zipped form and an image of the mask as jpg.
Say I have a userform that has a bunch textboxes on it. If I wanted to take 20 of those textboxes, and scan through and count which ones have values, is there a simple way to do that?
I need to make it so that if more than 11 of those 20 textboxes contain text, a msgbox will popup and tell my users they are exceeding their limit.
I was thinking one way may be make them all a list, and then somehow count through the list and once the count reaches a specified number it msgbox them...but I'm not sure exactly what the process is for that.
Essentially the user will enter their responses from a drop down list (assigned to about 30 textboxes in the userform: named txt1code, txt2code, txt3code....txt30code) and at the bottom of the userform the number of responses ('Yes') will be counted in real time and displayed in a textbox. (without using a command button)
For example:
The possible responses in the textboxes are 'Yes', 'No', 'Not Applicable', ''.
Yes = 1
I'd like the calculation to ignore No's, Not Applicables and ; however, count all the 'Yes' responses.
If there are 17 'Yes' responses, 10 'No', 2 'blanks', and 1 'NA' in the form, the Totals textbox will display '17'. As the user is entering information-"Yes" responses only (added or deleted), he or she can visually validate that the number of 'Yes' responses has changed.
Is a real-time calculation possible in a userform?
I am taking a range of cells (C22:D67) on several sheets ( same cells on each sheet) 4 sheets in total, each range appears in it's own text box on the single user form.
- I would like to know if there is an easier way of doing this, and can I leave out the cells without anything in them?
i have 2 userforms one with textboxes and the other one with listbox and textboxes.
Everytime user input their data(ie:first name, last name, address etc) in the first form the data's going to be saved in Worksheet("customerSheet") and later on to be displayed in the second form. using the listbox you can select the customer's name and the customer info will be displayed in the textboxes.
In the data sheet I have in F1 "Germany", F2 "France", H1 "Belgium", H2 "Poland", H3 "Russia"
In each of the sheets (appart from data) I have a validation list in cell D6 listing the data sheet values F1,F2,H1,H2,H3.
Now basicaly what I need is when I select Germany from the list it would take me to Germany sheet, when I select Belgium from the list it would take me to Belgium sheet etc.
Basically the sheets have always same name as the name in the list. I know i can write the code in individually but because I have a lot of these I was hoping there might be an easier way?
I have a long list of tabs listing "projects" which have changing names - on the first sheet, I want to have the table of contents automatically update and link to each tab - I want the user to only have to change the tab name to have the table of contents and link update -
I have a raw_data sheet with a list that is linked through Insert> Name>Define. This list is used for Validation Data List drop down menus in other sheets. I would like to update a list element in my raw_data sheet and have the corresponding element in other sheets also update. Is this possible or is there another way to do this?
I've been referencing sheets in my code directly with things like
Sheet1. Range(NamedRange)
however in recreating a new workbook (to reduce bloat), the sheet-numbering has changed... to avoid having to mess around adjusting things again, I was wondering if it's possible to do some sort of indirection... something similar to
I have an userform with 10 txtBoxes that I want to have the color changed based off another textbox Values (they will side by side, so when the user enter a value, it will know either it's excelent, good, average or bad based on the colors green, blue, yellow and red).
Is there a way to do so? Because all I have managed to do is to change one txtBox color per time, based on the value entered on the same text box (i.e. if I change the text box which should be changed, nothing changes).
How can I select sheets in a workbook based on the premise that the sheetname does not have the letter "Q" in it? New to forum, so apologies if format is not kosher. I have a group of 50+ workbooks which are all set up in the following format:
-Contains 30+ sheets. -Sheet names are varied, but follow a pattern. I try to keep the sheet names consistant on all workbooks so that it is easier to reference, but other users make this impossible. -However, I managed to keep two things consistant: There are two categories of sheets. In one group all sheetnames have a "Q" in them and in the other group all sheetnames DON'T have a "Q" in them.
Here is my problem: I need to run a macro to perform retative tasks on the GROUP OF SHEETS WITHOUT A "Q". To begin the macro I need to select these sheets and copy them to a new workbook, but since the actual sheetnames are varied, I am having trouble coming up with a flexible way of selecting sheets. The closest I have come is using a IF ... LIKE ... THEN statement, but I can only get it to work to select the sheets with a "Q" in the name and not the opposite.
I'm trying to create a code that will convert a column into another pressure unit. And then go back and forth as a button is pushed. I have been having trouble getting the units to switch from one to the other and I think it's because I can't get the line to re-write a cell correct.
Sub Macro5() If InStr(1, ActiveSheet.Range("M4").Value, "psi") > 0 Then Dim rngData As Range Set rngData = ThisWorkbook.Worksheets("Data").Range("M6:M10000") rngData = Evaluate(rngData.Address & "*14.22334") Worksheets("Data").Range("M4") = "psi"
i have a spreadsheet that has 3 shifts. its a weekly data sheet. i have 52 sheets for every week of the year. so far i have a "data" sheet aswell. this is the first sheet. I use this data sheet to generate the information or the other 52 sheets. example is you enter the date of the first monday of the year and it generates the dates on all of my sheets for the whole year. i would like to do something similar for the shifts. i would like to have a cell the enter the shift of the first supervisor on that first monday and have all the shifts generate. 1=nights,2=days,3=afternoons. but i do not want the number to appear i would like the word. the set up goes like this:
DATA E9 = the first number for shift to generate all other numbers
A5 = cell to display first shift A22= cell for the next shift A39= cell for last shift
on sheet1 i have A5 as =sum(DATA!E9)
i would like A22 to be the shift after example if A5 as 2 for days than A22 would be 3 for afternoons and A39 would be 1 for nights.
Currently we do a lot of report crunching using Crystal Reports. We will then dump the data into an Excel sheet to run varying pivot tables or databases off of.
Often we will have a field that the formatting will not match up to anything in Excel. You can change the cell to Text, General etc. The main problem associated with this is running VLOOKUPs. The item we type in will not match up to the data imported from Crystal. We have to F2, the new data before it will match up which is a pain considering there can be anywhere from 30-1000 rows.
I'm trying to solve this problem in 2 ways. First can anyone tell me an easier way to do handle this? Second, after trying many other things I thought about a macro that would automate the F2 process.
I copied a series of email addresses that I would like to convert into text. The email addresses, when hovered over, have a mail to: in the address. If I remove the hyperlink, all I get is the person's name but not the actual email address. How can I get just the email address?
What is the formula to cover multiple different text that could come up? I could have an answer that is NA, Yes and also a blank cell and if I could I would like to have it set up as follows:
I am developing a form to get the user's name. I want to put some default text in the textbox where the user types their name. I want the form to delete the default text when the user clicks on the text box or after they start typing their name.
Attached is the form and code that runs the form : UserName.xlsm
I would like to change the color of my text on my X-axis of a graph. I would like to have 4 or 5 different colors to identify different categories. I see how I can change all the text but how would I change just the text in column 1-3 to red, column 4-7 to blue, etc...
I am trying to simply change the text inside a textbox named (TextBox 2). My current line of code is giving me the error "The item with the specified name wasn't found". Any thoughts on how to troubleshoot this?