when "Update"(code is under "Update"button) button is pressed to copy the data from userform to the database sheet exactly into columns where both column heading match, for example if userform has heading "Qty Received " all data from that column should be in the database column with the same header "Qty Received"
I attached my file when you will open the file you will find screenshot how it should look.
Is it possible to modify the attached code so that it will copy bold text and border as shown in attachment sample1 and paste in sheet Shop. Currently the code just copy's and pastes without bold text and borders.
Sample1.PNG
The code below puts a green border around the cell that is beneath 10 in my chosen range, however I wish to add the border to the row of information instead of just the cell. My columns of data are from columns E to M, but the criteria for whether or not the data gets a green border is in column D....so lets say D15 is less than 10, I would want a border to go around E15:M15.
Sub Test()
For Each c In Range("D2:D350")
If c < 10 Then
c.select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
I've adjusted a jonmo code to add an item in col B which is not in col A to the bottom of col A. - fab code, thanks jonmo.
But.. i want to:
insert rows beneath those in column A to accommodate the added items and shade those cells in list A once they been added ( so the users now they've been moved )
I've posted the code below ( including my attempts at colour change where it shade the right cell but in the wrong column ) ...
Assume I have a cell M24 with a formula like
=M10 + $H24 - $I24*0.35
As you can see B10 is a fix reference (due to omitted $) which should NOT be auto-adjusted but be kept.
Now I want to copy the formular to lots of cells below cell M24. therefore I mark cell M24 and click copy in context menu.
Then I drag/expand the blinking cell border to lets say the 20 cells below. As I result I expect e.g. in cell M25 a formula like
=M10 + $H25 - $I25*0.35
Unfortunately I got
=M11 + $H25 - $I25*0.35
So the fix reference is adjusted as well.
How can I tell Excel 2007 to NOT auto-adjust fix references in formulas?
Using the code below, I search a database (Sheet1) on a pallet number and display in the listbox all the items on that pallet.
Assuming now that the pallet has been "worked" or despatched, what code would I use to delete that pallet and all its items from the database?
Private Sub cmdFindAllPal_Click() 'Find all Pallet
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD As String
Dim head1, head2, head3, head4 As String 'headings for list
Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("b7", Range("b65536").End(xlUp))
strFind = Me.TextBox2.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues, LookAt:=xlWhole)...........
I have created a form that will add new records to a database. On the database there is a predetermined ticket number. The "Create" form looks for the first blank row and adds the information on the form the database.
What I am trying to make happen is when a "Ticket Number" is entered in the appropriate field on the "edit" form, the "Date Raised" and "Raised By" fields populate based on the information stored on the row for the ticket number enter.
I have created a userform on the "Database" sheet that has a listbox at the bottom that populates with records when I search for someone using the 'Name' box.
The problem that I am having is that when I enter "Person" and get the 30-odd records appear in the listbox, I click on say Person 5 BUT the userform fields do not update themselves fully with the correct information.
I found the following thread ....
I am trying to create a relatively small database that is updated by users through a userform, but also has the capability to have columns (attribute categories) added or deleted without code modification.
I think my starting point, though it works for the simple case of no updates without code modification, is not good for my actual goal.
I have a database of 13 columns and ever increasing rows.
I want the following to be there on my userform.
1 combobox: showing the list of categories from which to search.(The categories are the column headings in columns A1 to A13.The user will have to select one category.
1 textbox:Here the user will enter the search term.
1 Command Button: When the command button is clicked/entered, the code shall be such that it will search in the column corresponding to the category mentioned in the combobox and display the results( The entire 1 row x 13 cloumns containing the search term) in a Listbox. If the search term does not matches then a message box should appear with the message "No entries found"
[b]
1 Listbox:to display the search result as mentioned above.
I am working on a label printing set-up for my work. So far I have completed the userform that formulas will be entered on and printed from. I had an idea of being able to save these formulas for recall later. I included “Save” and “Recall” buttons on the userform. My plan is to have my co-workers click on the “save” button and be able to enter an additional piece of data and have everything saved to another worksheet labeled “database”. If you look at the said sheet you will see a column for “customer”, “color” and then the colorants. Due to the wide variety of colorants and quantities available, I set it up so that I have the colorant and under that Oz and 48ths. I am trying to figure out a code that will populate that across the row.
Then I want to be able to push “recall”, find my customer and then color in a combobox and have that populate my label.
At this time I am stuck. Part of my problem is I don’t quite know the terminology to google.
Attached is the file I am working with. The only sheets that need concern anyone are "main" and "database". The others are from the original file and I will be deleting those once I am done.
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...
Currently I have a database in an Excel template. After a user creates a new workbook from the template, a macro button on sheet 1 brings up a window (a userform) to allow selection of items from the database sheet. The item selected is entered on sheet 1 by means of VLookup. That works fine, but to edit the database correctly it is necessary to open the template itself. This is not a user-friendly method.
I figure the best way to solve this dilemma is to separate the template from the database. That is, make the database sheet into a workbook. This I've done. Here's the problem: What code is there that links the list box in the userform (of the template) with the closed database workbook? Is it even possible? If a file path type code is required, it may work on my computer, but when I use my template and database on another computer, the code doesn't work.
Another problem, and similar is this: I'd like to be able to have a macro button that opens the database from the new workbook (which was created from the template) in order to edit the database. If having a template seems to be my problem, I must have it since each workbook created from it is a different project.
Below is my current code. The strFind1 searches for a name within the database and then I need strFind2 to do a exact for a Subproject search and a partial search for everything containing the Subproject selected and other Subprojects. Currently, when the database entry in the worksheet includes Subproject 1 the search function works but when I have an entry that contains Subproject 1/Subproject 4 it does not find the entry. How can I expand the strFind2 to equal what is selected in the Combobox2 and find entries that have what is selected plus more text. I have set the line where I think everything is going wrong to a bold format.
[Code] .....
I would like to add a piece of code into the user form that will check and verify if a part has already been added so as to avoid multiples in a user-driven/created database.
here is a repost of the current code i am using for the user form (I have posted it before in another thread .. Blane245 helped me out with a different question I had)
I use the below code to specify where a database is:
View 2 Replies View RelatedI use the follwing code to update an access database from excel.
View 2 Replies View RelatedI wantto prepare a database in excel. This is database of five different excel files.Consolidate them in one excel file under five sheets.
Thesefiles have name say A, B, C, D, E. Macro should ask user to browse these filesone by one and copy data in new excel under individual sheet. Finally databaseshould save as name X and should contain sheet 1 as A sheets 2 as B etc.
Cell A1 is HEAT_CODE
Cell A2 is W5H
In cell
=DMIN(Database,"Cost",A1:A2) return the proper value
I need something like the following so I can use fill down.
=DMIN(Database,"Cost","HEAT_CODE"=A2)
the above returns #VALUE! error.
I have the following ....
View 14 Replies View RelatedA board member helped me with a macro, but my example had the wrong columns, and I did not know how to adjust the macro.....
I have 3 columns.
Column 'W' Needs to return a value derived from 'fourth and fifth character' of Column K.
Column 'X' needs to return a value for the Sixth and 7th Character of Column K.
Column 'K' is a 7 Alphanumeric part #. example AI3-HDSS.
(first three alphanumeric characters change, but are not relevant.
The 11 combos are all the combos in the spreadsheet.
Col. Y
Col Z
Col AA
Col AB ...
code is pulling data for forecasts for the following 10 days, and the code is the following:
WHERE (((dbo_ACTUAL_HDD_DAILY. DATE)>=Now()-1 And (dbo_ACTUAL_HDD_DAILY.DATE)<Now()+9)
All he wants modified is to pull data for the entire current Month (Ex. if it is in the middle of July, he would want the data from July 1-July 31, or if February from Feb 1-Feb 28) It would be nice to do this without having to change the VBA every month.
I am trying to find a way to have a cell look into a group of other cells and display the first available things it comes to. Then have the next cell look in that same group and display the next item.
cells A1:A5 have 3 pieces of information in them scattered among that column (A1, A3, A5 might have the info in it one day, then A2, A3, A4 the next day)
I want B1:b4 to find the info in the A1:a5 and display it in order as it appears in the A column.
I am building a sheet of sales targets for 2009. With each month allocated a certain percentage of the annual target.
I wish to be able to take into account a change of target at some point in the year.
If i were to change the annual target in June, i need the spreadsheet to only change the monthly targets from June onwards, January - May are finished.
In the example attached there is a change in annual target in June. How do i calculate what the remaining month's targets need to be in order to meet the annual target while taking into account what has already been achieved and the shape of the budget as indicated by the percentages??
i have database where are some data for several shops i need macro which will copy data for exactly that shop which i will choose in summary sheet, for better understanding i also attached excel file.
View 1 Replies View RelatedI would like to make a piece of VBA code that will copy the data from B3:C11 (fist sheet) and put it into the individual columns (second sheet). The columns are named ID, name, e-mail, phone, value, address.
The second sheet should therefore act as some kind of database, where new entries are put below the last one. Also, it would be great if the program checked at first whether the ID is already written in the sheet2(database) and if so, it would write an error message.
Included : samplexxx.xlsx
Tried the below code with minor modification to select file referred from
Open 132 files and copy data into master file
My problem is its not copying the entire data. Copies only few 3 or 4 rows.
Code:
Option Explicit
Public Sub CommandButton2_Click()
Dim Master As Workbook
Dim sourceBook As Workbook
Dim sourceData As Worksheet
[Code] ...........
When I run the userform initialize procedure to reset the values in text boxes and the like instead of resetting like it should, now it closes the userform completely and then won't allow me to show it again, what could be the problem?
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
TextBox3.Value = ActiveWorkbook. Sheets("Sales Invoice"). Range("G15").Value
Dim hWndForm As Long
Dim hMenu As Long
hWndForm = FindWindow("ThunderDFrame", Me.Caption) 'XL2000
hMenu = GetSystemMenu(hWndForm, 0)
DeleteMenu hMenu, SC_CLOSE, 0&
End Sub
The Dim stuff down is to gray out the x button, there are also some module level declarations to go with that...
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Private Const SC_CLOSE As Long = &HF060
the userform shows when the workbook opens and is then hidden when the user has finished entering values, then when the user goes through the process of being asked to print and save, they are then asked if they would like to create a new record, if yes then it shows the userform again, but the userform is still filled with all the stuff previously entered. I tried using the unload me instead of hiding and that wouldn't work at all, didn't give errors just didn't show the userform either, this at least shows the userform, but now when the user goes to clear the information by initializing the userform again, it simply closes the userform and then it can't be shown again either.
[url]
This is the link to the ZIP, and here are some instructions for setting it up to work.
The contents of this need to be unzipped into a folder called SyntheticShield that is placed in the C: drive that way all paths begin with C:SyntheticShield and then the other folders or files will be referenced correctly. The template I'm having problems with is the SyntheticShieldInvoiceMaker template, when you open it you'll be prompted whether you want to create a new invoice, number is final blah blah blah, say yes, then it will ask if you are importing from the Quotemaker which you aren't say no, it should then show the userform as it should. Then you can go through that process and by pressing either the create invoice or go to template directly whatever, you can hide the userform. Then you press the command button (red) a userform is brought up asking if you'd like to save without emailing, save with emailing or close controls, as for right now, I'm just getting the save without emailing to work the rest is all the same just a few tidbits of code. So click save without emailing, it should then prompt an are you sure message box, click yes it will do some things, then it will ask if you want to print, click no, then it will ask do you want to create a new invoice, click yes, this should then start the process all over again by calling the workbook_open procedure, however, when you go through everything, and click no to the import from quotemaker part it won't show the userform. And at one point it would, but then I couldn't initialize the userform without it disappearing and not being allowed to be shown...I tried putting a command button on the template to show the userform, but it wouldn't do it either.
I'm trying to determine how to indicate which month an adjustment will post to an invoice.
Column A= billing cycle date
Column B= Market
Column C= Adjustment Approved Date
Column D= Adjustment Amount
Column E = Which invoice will credit post to:
So I'm trying to build a formula in Column E that will look at the cycle date in Column A compared to the Adjustment approved date in Column C and then kick out which invoice the adjustment will appear on. The values in Column E were placed mannually to show what I'm trying to accomplish. if the adjustment approved date is = to a cycle date it will show up on the same invoice. ie if approved on the 1st and the cycle date is the 1st the invoice will reflect the approved adjustment.
ABCDE1Cycle Day of MonthSales MarketAdjustment Approved DateAdjustment Amountposted invoice21Salt Lake12/15/2011-$1,300.00Jan '1232Denver12/22/2011-$3,802.01Jan '12411Atlanta1/12/2012-$5,292.00Jan '1255Dallas1/23/2012-$6,000.00Feb '12628New York2/1/2012-$5,000.00Feb '1272Denver12/5/2011-$500.00Jan '1283Seattle2/4/2012-$440.74Mar '12912San Diego1/4/2012-$500.00Jan '12101Phoenix1/17/2012-$257.87Feb '12112Denver1/18/2012-$1,220.92Feb '12123Seattle2/5/2012-$911.03Mar '12134Spokane1/30/2012-$20,391.86Feb '12145Dallas12/6/2011-$45.63Jan '12151Phoenix12/7/2011-$7,176.14Jan '12
I need to paste a picture from the Clipboard to my Worksheet. I select the origin and paste it with the macro.
I need to adjust that picture to fit in a defined space from left corner of Range($J$10) to the right corner of
Range($BJ$35)
Actually, i'm using this procedure
ActiveSheet.Unprotect
RANGE("graphique_PL").Select
ActiveSheet.Paste
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 358.25
Selection.ShapeRange.Width = 725.
The problem with it is, the Height and Width is arbitrary to the size of the cells at the moment. I would like to had a procedure to calculate does value. They represent the distance between the defined cells location for the image. Actually, if cells width or height change, the picture is misplaced.