I have created the attached spread sheet to work out and calculate gp on products. Sheets and userform1 are working perfectly how ever userform2 wont load and I believe its something to do with the named range? The idea for this userform was to select a product from combobox1 and then change the price on sheet1 and clear the price on sheet 2 in the different sizes.
Also userform2 i would like to create it so that if any of the fields are left blank it wont change price of selected field in sheet 1 and wont clear selected area in sheet 2.
Finally is there a way on userform1 to shrink the userform size depending on how many sizes are needed?
I have created a Excel userform. Since it has lot of controls, it takes good amount of time to load.
Is there a possibility of displaying a progress bar till the userform is loaded.
Google search has shown me ways to create a progress bar using Excel Forms. However, I am looking for a way to show the progress bar till the main userform is loaded.
I made a template file with a userform included to enter data into a protected sheet at the command of a button. When I open the template, everything works fine. However, when I use a macro to create a new workbook and import the template to create a new sheet (or several) the userform suddenly disappears! The template is saved as macro-enabled and the new workbook is saved the same way. What am I missing that I'm loosing the userform??
How to get a listbox to populate from a database sheet the code I am using to do this is posted here and it works great. My problem is now I want to get the Listbox1 results to populate my UserForm5 textboxes from the row selected in the ListBox1 of Userform4, but I am getting the error "variable not defined" and the "rw" gets highlighted. as seen in the code below it.
UserForm4 portion of the code that does the search of the sheet that holds the data
Code: Private Sub CommandButton1_Click() Dim sFindText As String, sFirstAddr As String Dim i As Long, lFindCol As Long, lCol As Long, lRow As Long Dim rMyData As Range, rMySearchField As Range, cFound As Range Dim vArray() As Variant
[Code] ......
Userform5 issue is this rw in red doesn't seam to be right
Userform Loading with database turns to close all Excelworkbooks completely
Database created in Excel worksheets to load and show in various different objects on userform. At present this project file size is approximately 2.5 mb and more to go as it is not yet complete. Now you can imagine how much big this project can be.
It uses various different types of objects such as listboxes with many columns, combo-boxes, textbox, checkbox and many. Every objects has its own style to display data on userform which is set in its properties itself.
Before loading userform to display, program creates database from manual entry (which is made by user on their working sheets) to database entry sheets (which is made to make compatible to show on userform). Upon loading, it also loads/populate all datas required in their objects to display.
All works perfect when I am in editing mode. It doesn't matter how big the database can be and works as required. This Project is now Password Protected and distributed among all staffs in our company to work with.
Error Occurs, when I tested in non-editing mode. The moment I triggered the macro to load the program, it takes some few times and pops-up with Excel Recover error message box asking whether to send Error Report and to whether recover & start Excel application again??
I don't know what is wrong. Sometimes it also run successfully when in non-editing mode even there is no changes in program codes.??
Sometimes I feel there can be a virtual memory issue, but my Pc is more than enough with 1GB ram. I hv also increased virtual memory limits and tested but all vain attempt...
A custom UserForm pops up when a cell is double-clicked. The form contains a ListBox that presents the user with a list of values to select. When the UserForm first pops up I want the item that matches the value of the activecell to be highlighted/selected.
I want to load one with data from a worksheet query table that will vary in size each time it's executed. The length will vary, but the range is continuous, and five columns wide. I'd prefer a do while loop, looping until a blank cell is encountered. Can someone get me started with general syntax and setup of the array?
I have a userform that has Label 1, textbox1 and textbox2 at the top. (I shall call it HEADER)
Then, arranged in a row i have combobox1, textbox4, textbox6, textbox7 and label7 (I shall call LINE1)
On a row beneath this i have combobox2, textbox12, textbox14, textbox15 and label 10 (I shall call LINE2)
I need a way of finding the first empty row on sheet 1 (easy bit) and then putting the info from the HEADER and LINE1 in the first empty row, then HEADER and LINE2 in the next empty row etc etc. There are 5 rows in total
In my userform I have a lot of textboxes. When the user clicks "OK" the values of these textboxes are transfered to row 3 of sheet1.
For this I use some of the code below:
Code: If NaamTextBox.Value = "" Then ActiveCell.FormulaR1C1 = " " Else: ActiveCell.FormulaR1C1 = NaamTextBox.Value ActiveCell.Offset(0, 2).Range("A1").Select If GroepComboBox.Value = "" Then ActiveCell.FormulaR1C1 = "" Else: ActiveCell.FormulaR1C1 = GroepComboBox.Value ActiveCell.Offset(0, 1).Range("A1").Select If TypeTextBox.Value = "" Then ActiveCell.FormulaR1C1 = " " Else: ActiveCell.FormulaR1C1 = TypeTextBox.Value ActiveCell.Offset(0, 3).Range("A1").Select If TypecodeTextBox.Value = "" Then ActiveCell.FormulaR1C1 = " " Else: ActiveCell.FormulaR1C1 = TypecodeTextBox.Value
This goes on for about 55 columns. Also, not every column is used as you can see in de code above.
It seems to me that this is not smart coding, so my question is: can you make this more efficient?
I did find some nice code with uses EmptyRow (find first empty row) but in my case it's always row 3 where de input is placed. After this row follows much more data.
I have two sheets of data and two tables (1=finial 2=data comparison) I am trying to figure out some kind of formula or vba that can easily tell me that the location point in table one data comparison matches the location in table two. The VPMDFS (location footage) in table two or NDE data can be a range, say vpmdfs.NDE + or - 3 ft. so the ILI or source record should fall in between this range. Once the match has been made I need to copy and paste that record onto sheet one all in the same row. I have been scratching my head and researching for weeks and cannot come up with a simple solution. My problem with all the scenarios that I've tried is the formulas what the data in the data comparison sheet to already be in the same row. Please reference my attached work book for examples.
I want to be able to find all the 0 differences (i.e debit = credit) cut that group out of that sheet and paste it to a new sheet in the same workbook. I have the code to find the 0 difference but I don't know how to select the range that makes up that difference and cut and paste it to a new page.
In another cell, I use Data.Validation.List and use "MyList" as the source.
Now I find another range named "HisList". Is there some way to determine if "HisList" is being used as a source for some other validation somewhere in the workbook?
The data I need is in the middle of the worksheet (A16:B41), is there way to define the range so that excel only looks at that range and finds the last row of data?
Is there a property to, given a certain range, return the name of the range?
I have used the name property and gotten a name in the form worksheet!address, but I am trying to return the name that I have given the range.
I was planning on using:
'returns the name if it exists, else returns "" Function findName(myRange As Range) As String
Dim name As name
For Each name In ActiveWorkbook.names If name = myRange.Address Then findName = name.NameLocal Exit Function End If Next
findName = "" End Function
However, this code isn't working because when comparing name to myRange.Address, name includes the worksheet that the range is on, but myRange.Address doesn't.
Even if I get the above code to work, it will take quite a while because I have many names in my worksheet, and would be running the findNames function many times
I have two columns (one is mean and the other is max) and the third column, I have the corresponding letter. I have in total 5 rows with mins and maxes and corresponding letters. I want a function that when i put for example #3 on cell D9, then my cell E9 will spit out the corresponding letter.
See the attachment : find the corresponding letter.xlsx
Above is one of the formulaes that I am using and I tried editing, to no avail.
Anyway here it is, I need to search E4 - E30 for a particular name, lets say Pandy. Now if pandy is found, then a cell on another tab has to equal a cell which is 2 cells on the left. from which the cell which contains the name is located.
here is a psuedo version of it...
Search E4-E30 Find a cell which contains 'Pandy' If pandy is found, 2 cells to the left then a cell on another tab is equal to that.
I currently am working with a worksheet that has over 250,000 data points. It is a spreadsheet for a company in which different work order are organized. A given work order can have several parts (ranging anywhere from one component to 40ish) and thus the height of the cells in which each specific work order is placed differs from work order to work order. For each component, the spreadsheet tells you how many can currently be made based on the materials available. What I would like to do, for each specific work order, is go through the data and simply tell whether or not the work order can be completed (the quantity of each specific component needed is also given in the spreadsheet) and then report out exactly how many components can be made.
Ultimately, this means that a "limiting factor" analysis must be done. For instance, if the work order is asking to create plastic bottles that consist of the bottle, the cap, and the label and it asks for 3 bottles but only 2 caps are able to be made, the limiting factor would be the caps and thus only two bottles could be made and the work order could not be fully completed.
The spreadsheet is set up based on another worker here, I am just trying to come up with a way to analyze it. Cells A1:Axxxxx are the work order numbers and the size of these cells differ because they are merged to fit the number of components they have. The componenets available are then ranged C1:Cxxxxx etc and the quantity needed are also listed in a similar fashion. What sort of structure could I use.
How can I determine what the bottom row is in a range in VBA? I have an SheetChange event sub that takes in Target as Range. I want to know what the first/last row/column is in the Range. So, for example, say the Sheet has values in A1:B5 and I paste over A1:B4. Target will be A1:B4. I need a method that returns 4. I tried Target.End(xldown).row, but that gives me 5 (since theres data in A5).
I have the code below which does work and looks up a piece of text in A1 (in this case the word exhaust) within the contents of column B. If the text is found (either part or whole) it returns a Y and if not an N in column C.
It works fine but I can't work out how to get it to repeat should I then want to look at a value in A2, A3 etc without removing any Y had Y already been displayed due to a prior word in the list. Below are the results I'd want.
Sub FindSomeText() 'Application.ScreenUpdating = False Range("C1", Range("C65536").End(xlUp)).Clear keyword = Range("a1").Value Range("b1").Select Do Until IsEmpty(ActiveCell) If InStr(1, ActiveCell.Value, keyword) = 0 Then
I am trying to update our sickness spreadsheet for work to make it a little more 'intuitive' and less cumbersome to use. There are dates (by day) accross the top, with staff names below. If someone is off sick, the manager writes the number of hours lost to sick in that day, under the correct day, then uses back color / font color to show its sick.
Our work uses a 3 periods of absence or more over a rolling 12 months and the staff are issues with a warning, same with over 8 days off sick. If they are off for longer than 14 days they are long term sick.
I would like excel to show whether each staff member has met any of those criteria in the last 12 months by either highlighting their name or putting text into an adjacent cell.
I have tried using offset, named ranges and anything else I could think of to automatically increase the range +1 day very day, but I cant !
I have a set of data that has repeating column headers. So, on a sheet called "January" I have "Forecast" and "Actual" headers that repeat over and over underneath "Week 1", "Week 2", etc. On a different sheet (called "2014"), I'm trying to write a formula that will produce the latest or rightmost number underneath the "forecast" header. What I want is to be able to input data into the "January" sheet every week so that I have a record of progress and I want the "2014" sheet to show the latest data for every month.
