I was given the code below which works for "sheet1",but when adapted it to write to sheet repairs as well, nothing writes to sheet "repairs", have tried different codes that works writing to 1 sheet , but am having trouble writing to both sheets from a macro,i know it can be done
[Code] .....
I create a input user form,that add data to a sheet. This user form put 4 kinds of data to the sheet, 2x text box, 1 comobox and date of the moment.
But based on what I select in the combo box that should be written to a specific(equal named) worksheet.
festival boekhouding 2.0.xlsm
Trying to write a UDF Code for a User Form. The User puts in the data for the element it is trying to find the pressure in mm Hg for.
I want a Msg Box to appear when the desired temperature is less than the lower limit temp. and the desired temp is greater than the upper limit temp.
I figured out how to get the message box to appear, but I have not figured how to get the If statement to continue with the calculation if the desired temp is within range. When I press the command button to calculate, the message box appears whether the temp is in range or not.
My code is as follows:
Private Sub CommandButton1_Click()
Dim A As Double
Dim B As Double
Dim C As Double
Dim Pvapor As Double
[Code]...
I have a question related to Userform and/or Message box. In case a macro solve and displays the result as in Userform and Message box then is it possible to write (type) the values to the excel sheet without closing the Userform or Message box ?
View 2 Replies View RelatedI have two userforms. The 1st is called “add a facility” and the second is called “Edit Facility”.
The first userform works pretty well. It places all the data in the right columns in the first empty row at the end of the table. Then the “sort” procedure places the “new” facility data in the right category. Everything is good there. Here's the code.
Private Sub CmndInput_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Facilities")
' find first empty row in database
iRow = ws. Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
'check for a Facility
If Trim(Me.TextFacility.Value) = "" Then
Me.TextFacility.SetFocus
MsgBox "Please enter a Facility"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.ListBox1.Value
ws.Cells(iRow, 5).Value = Me.TextFacility.Value
ws.Cells(iRow, 4).Value = Me.ListBox2.Value
ws.Cells(iRow, 6).Value = Me.TextType.Value
ws.Cells(iRow, 7).Value = Me.ListBox3.Value
ws.Cells(iRow, 12).Value = Me.TextConstneed.Value
ws.Cells(iRow, 16).Value = Me.TextPREAPP.Value
ws.Cells(iRow, 17).Value = Me.TextPreapsub.Value
ws.Cells(iRow, 3).Value = Me.TextDescription.Value
ws.Cells(iRow, 8).Value = Me.ListBox4.Value
ws.Cells(iRow, 9).Value = Me.ListBox5.Value
ws.Cells(iRow, 2).Value = Me.txtRefnum.Value
ws.Cells(iRow, 21).Value = Me.txtappdate.Value
ws.Cells(iRow, 24).Value = Me.ListBox6.Value
ws.Cells(iRow, 20).Value = Me.txtsubdate.Value
ws.Cells(iRow, 10).Value = Me.ListBox7.Value
' SORT Macro...
I have a workbook with 180 Sheest. I need to copy sheet name and paste to column Name. In the Column Year write 2013.Finally I need to all sheets as show below in in Sheet Farms. Doing this one by one is time consuming and with error risk. I think a loop can do this,nevertheless, I don't Know how to do it. Column size can be different in each Sheet
Sheet name Famr1
CodNameYearDescTotal1Total2ProvCnt
1234Apples200xxxyyyzzz
3412Bananas400xxxyyyzzz
2358Oranges500xxxyyyzzz
8956Pines800xxxyyyzzz
[Code]....
The attached excel file arrivals page and departures page Serial number to compare current on the data up-to-date page, I want to copy.
up-to-date on the "F" column is copied to the page on which you need to print.
I am having a problem using data from my combobox to find the cell on the worksheet and write to the adjacent cell.
Worksheet name is Fleet
ComboBox Name is ComboBox7
TextBox name is TextBox3
CommandButton1
I would like to be able to take the text from ComboBox7, find this text in Col A of worksheet Fleet and write the Value of TextBox3 in the adjacent cell in Col B. when
the CommandButton1 is pressed.
I have workbook with 12 sheets in it. each sheet is identical except that they are for each month of the year. i am using a userform to enter the data into each sheet. Each sheet will have different data.
I want to be able to use the same user form on each sheet but still have them insert into the open sheet. Currently I am creating duplicates of the userform for each sheet but it gets VERY!!! tiresome and it is making the workbook VERY large (5 mb so far). For example. the first sheet is "april". i open the userform and enter the data into the userform. the userform enters the data into the sheet.
now i go to sheet "may". currently I open a userform in that sheet that looks exactly the same as the userform in sheet "april" but is a completely different userform. I want to be able to use the userform from sheet "april" in sheet "may" but when in sheet "may" it inserts into sheet "may".
I have created this with text, command & list boxes with a view to creating an asset register.
So the options i have are premises, hardware, software & fixtures. I have tested so that these go onto 1 sheet ( at the moment all additions go to premises) but i require for it to go to there respective sheets i.e. a sheet for each asset.
But, due to inexperience with userforms and VB i don't know how to do this.
Also, how can i get a sequential number coming up for each asset on their respective sheets
I have a problem with value's from a userform saving in the sheets as date's. In the userform i fill out e.g. 14-4-12
With the code beneath i save them in mij sheet.
Code:
Sheets("Containerlabels Afdrukken").Range("D6") = TextBox2.Value
When i save this in my sheet, it is saved as text. While i want to calculate with this value as a date, i would like to know how i can save this value as a date.
user form that has a list box that only picks up visual worksheet (not hidden ones) and then allows the user to select which ones to print via check boxes.
View 5 Replies View RelatedIs there a way to use a userform and hidden sheets at the same time?
I want to limit the access to the information through a userform but my userform requires me to unhide the userform to show. Is there a way to go around this? Because the information might be misused if the user can simply close the form and have access to the information in the sheet.
Let's say I have Sheet1-Sheet6. I also have combobox1 (with item 1, 2, & 3 as the list items) & combobox 2 (with items 1 & 2 as the list items).
If combobox1 = 1 & combobox2 = 1 then hide Sheets 2,3,4,5,6
if combobox1 = 1 & combobox2 = 2 then hide Sheets 1,2,4,5,6
If combobox1 = 1 & combobox2 = 3 then hide sheets 1,2,3,4,6
If combobox1 = 2 & combobox2 = 1 then hide sheets 1,3,4,5,6
If combobox1 = 2 & combobox2 = 2 then hide sheets 1,2,3,5,6
If combobox1 = 2 & combobox2 = 3 then hide sheets 1,2,3,4,5
I would like to also make both of these combo boxes required fields and to default text to say 'Select One...'
I am trying to set up something similar to the old "print dialogue" box whereupon the user presses a command button on the userform and all sheets within the work book are listed (eg:sheet1, sheet2 etc) The user can select any amount of sheets and and using "OK" outlook is fired up- pretty similar to Ron de Bruins code for email.
I have tried taking the print dialogue vba and stooping it at copy, then trying to gets Ron's code to continue- without success.
There are great bits of code I've seen for select tabs and selct sheets from listbox, but I have found none to email once selected.
I have a workbook with 250 worksheets and I'm trying to build a single userform to work with all of them and I'm using binding to do that. Reason for using binding is that information is scatered all over the place, I mean a single column contains more then five different types of data with blank columns left in-between to be used as seprator, I know it's one of the worst ways to arrange data. Anyhow, the best part is that all the worksheets have the same type of information in their cells as of any other worksheet so binding does seems to be a good way to go ahead but only if it's dynamic enough to change accordingly with the worksheets.
Now I have already created a navigation-bar for it so I can use the same code for a drop-down menu list but what worries me the most is can I able to use a single form for all the worksheet and could it be done through binding?
I have a userform that enters data into a hidden sheet, and it works fine so far, but now one of my bosses would like another sheet that contains some lookup data for the form to be updated every time the form is used. I'm thinking I can handle the data in the lookup sheet automatically deleting the older data but I can't figure out how to send the data to two different sheets when the form is completed.
Private Sub CmdOK_Click()
If MsgBox("Are You Sure This Form Is Complete?", vbYesNo) = vbNo Then Exit Sub
Unload Me
ActiveWorkbook.Sheets("Plant Production").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtDate.Value
ActiveCell.Offset(0, 51) = txtTime.Value
ActiveCell.Offset(0, 1) = cmbPlant.Value..........................
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.
Currently I have done userform which works fine. I have one issue:
Uploading data to sheets is extremely slow ( takes about 30 secs)
I suppose finding last row takes much of it. How to replace 'lastrow' concept?
[Code]......
I have a userforms which transfers data to protected sheets.The userforms worked until i protected each sheet.Can there be any code which i add to the current userform code so it can still transfer the data to protected sheets
The other issue also is now that the sheets are protected the Outlining also does not work.Can this also be changed so the Outlining works on protected sheets
I have 5 userforms which all transfer data.
[Code].....
I have created a custom Menu (excel add-in) to make my work easy in excel. My problem is to print only selected sheets from Workbook in one PDF file, for that I've created a Userform with 2 listboxes, add sheet and print buttons. In the first listbox are listed all the sheets and in the second listbox are the sellected sheets to be printed. What I've succeded so far is to print selected sheets, but it creates one PDF file for each sheet, only if I put my code in workbook and not in Menu add-in (.xla file). As PDF Printer I use PDF reDirect Pro v2.
View 8 Replies View RelatedI have a sheet with 3 macros. It consists of two macros to produce results, and the third to bridge the two together where is all starts. I have built a userform in the sheet and am trying to get the variables in both sheets, to be user changeable and selectable via the userform.
In the attached data, the userform allows for the selection of two sheets, A and B. The user would select in A the sheet with data which is equivalent to Oval_An and in B the sheet with the data equivalent to Oval_DMA. The first macro, Find_75 runs, followed by the second macro, kTest, all working to produce results when hitting the Subtract button on the form. The Find_75 tolerance are also defined via the useform box, as well as kTest's compare tolerance.
I have a wordlist (65000 words in Column B*) in a worksheet “w1” and poems (about 21000 rows) in another worksheet “w2” where first verse is always in B and second one in C. The column D of w2 contains of information such poems name, author’s name, book’s name and so on.
I want to search for those verses through a macro which contain words from my wordlist B* w1 and add references to my words that way. It’s about making a dictionary in which each word has a reference to a poem and verses in which it is used.
Column A of w2 has number in it which shows how many times these rows have been already used as reference. Column A of w1 contains of a value “1” or “0” in which “1” means this entry has already been processed or already has a reference and “0” means it has yet to be done.
If my word B* from “w1” exists in verses B or C of “w2” the results should be shown in an userform containing 5 text boxes with 5 results. In each text box a result should be shown “(verse B; verse C); (value of Col.A)” and a checkbox (or a button). That would mean 5 result, 5 textboxes and 5 checkboxes (or 5 buttons). I would like then to choose one of the results by checking the checkbox (or pressing the button) in front of the text box. After I have chosen the result the whole row from w2 should be pasted in following columns of B*. That means the cells from w2 column B,C,D would be pasted to column C,D,E of w1.
The criteria for the search in B and C should be the lowest value in A of “w2”. I mean the w2 rows with lowest A value should be preferred if there are more results. The result showing user form with 5 text boxes and 5 checkboxes(or 5 buttons) which gives me the choice to choose one of the results should also contain a button “search for further results“. For the case none of the results is useful.
As far there are many poems (verses) I would like to prevent using the same verses many times. The more different verses I use as reference for the words in w1 the better it is. Therefore every time a result is picked by me “1” should be added to the value in Column A in w2. And every time search is started the lowest A values should be searched first.
As soon a word has successfully got it’s reference the value in A w1 should be changed from “0” to “1”. And by next search all words with A value “1” should be ignored and only “0” words should be searched for.
Each time a row from w2 is used as reference, the B* word w1 should be added to col. E of w2. If used for many ";" should be the seperator.
Is it possible to have a single userform put data into two separate sheets within the same workbook.
I have a userform set up for entering client data into a quote register,with no problems ( sheet 1)
A1, A2, A3, A4, A5, A6, A7, A8, A9
What I would like is for the data to also go to sheet 2, where I have a form set up to print off for our technicians to take on site.
The problem will be that I would like the data A1, A2, A3, A4, A5, A6, A7, A8, A9 not to store on this sheet after it is printed, so the next set on entries , can be printed etc.etc
I am trying to create a way to protect and unprotect all sheets and the worbook by means of a Userform pop up interface.
I have managed to create the Userform but what the code will look like and where to place the VBA code that will make it work, Also i would like to automatically save the sheet/s as i switch between them. so that any updates made to the sheet will be automatically saved when i move to the next sheet and also when i protect or close the workbook.
I have a fairly elaborate close-down routine for my workbook.
1) Quit out of fullscreen (restore initial state of excel before saving)
2) Hide all sheets but one (enforces cant do anything unless macros enabled)
3) Automatically save without a prompt
4) Close the workbook
I've researched it well and implemented it to the point where it works, but only if the user clicks the excel application close button -- the "X" in the titlebar (hence not in fullscreen).
However, if I launch the process via a userform button click, then for some reason the
DisplayFullscreen = False, Sheets hiding, and save codes have no effect (fullscreen persists, sheets are not hidden, no file is saved) in the Workbook_BeforeClose() subroutine....
I repeat: all the above works fine if process was launched by "native" excel button, but does not have any effect if launched from a userform commandbutton. (?!?!) any ideas what's going on?
I'm including my code so you can get an idea how it's structured.... by it's pretty "by the forum" as far as I can tell...
Private Sub CloseButton_Click()
If(1) Then
'Method 1 - close directly from userform command button
With ThisWorkbook
'.RunAutoMacros (xlAutoClose)
.Close
End With
Else
'Method 2 - set a timer to call a function to perform
' "ThisWorkbook.close" in a function outside of the userform
CustMenuCloseTimer = Now + TimeSerial(0, 0, 2)
Application .OnTime CustMenuCloseTimer, "CustMenu_CloseAction"
End If
End Sub
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click()
Sheet7.Activate
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.RightFooter = " Construction Assumptions"
.PrintArea = "CONSTRUCTION" 'the named range to print
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1 'this changes depending upon the page selected
End With
ActiveSheet.PrintOut
End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate
'Sheet3.ResetAllPageBreaks 'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.PrintArea = "DEVBGTALL" 'the named range to print
.FitToPagesWide = 4 'this changes depending upon the
.FitToPagesTall = 1
End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
I'm wondering if an if then else function can be written for the problem that I'm having? Or is there a better way to accomplish this? I've detailed my problem within the example attached.
View 3 Replies View RelatedI have a question, that could be answered by the experts in this forum. Question: I need to write the letter "E" in the second column (B) when I find that any cell in a row is in orange color (color 44). The code I wrote:
View 3 Replies View RelatedI basicaly need to look down column Q8 to Q52 for the falue "C19"
If that value is present - Then I need to look up the value of associated
with what is in its respective cell in column O8 to O52.
Then add them all together.
I have the formula that follows and it works, however it is huge. I have to repeat the IF,Vlookup 44 times. Just wondering if there is a better way.
Here is the formula I am using.
=SUM((IF(Q8="C19",(VLOOKUP(O8,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q9="C19",(VLOOKUP(O9,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q10="C19",(VLOOKUP(O10,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q11="C19",(VLOOKUP(O11,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q12="C19",(VLOOKUP(O12,clusterequipmentvalues,2,FALSE)),0)),
.... all the way to C52.
