Is there a way to change a line of vba code using an input box?
The current macro downloads a file from our intranet. However the filename changes each week. It is only the last portion of the filename that changes since this part is the date it is created. e.g filename010507.xls filename090507.xls
What I want to do is for an input box to pop up, the user then enter the date and this will then replace the existing filename in the code with the new date.
I have a spreadsheet that has multiples ranges. Some values are displayed as negative values. I would like to create a macro that allows me to first select the range/s, (maybe using a input box) I want to change and then display the negative values as positive values.
Would I need to create a separate macro to do the opposite (change the values that are positive to a negative value)?
The other macro I’m trying to create is a similar type. What I would like this macro to do is first select the range I want to change and then divide all the cells in the range by 100000
So I found a macro that does something similar to what I need.
I tried changing the data range and input column but it doesn't work.
What the macro does is I type whatever name into the msg box and it will copy and paste all the rows that contains the name to a different sheet.
Here is the macro:
VB: Sub test()Dim vl As String, res, sh, myrange As Range Application.ScreenUpdating = False: vl = Application.InputBox("Enter value for Column B", Type:=2): If vl = "" Then Exit Sub
I need to input values rangeing from 0 to 5. When inputing I want to make the background colour change to a differant colour for each value. I can do it but only for data already in the spreadsheet.
Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.
When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.
Example:
B20= 1:15 (1h15m) NOT 1:15:00 AM
B24= :30 (30min)
B28= :45 (45min)
B32= 1:45 (1h45m) NOT 1:45:00 AM
B38= 4:15 (4h15min)
I have attached a copy of the form for better clarification.
It doesnt seem to be complicated, but it's certainly a lot more than I thought.
I'm a average excel user, i know my way around it and kow common and basic formulee, VB isnt a strong point, but i am willing to attemp anything for this, let me explain
Lets say in colum A I have a list of items, in colum B i have a check box, currently the check box is red, if i tpye a x it turns green due to conditional formating. That works fine...onto the problem...
Lets say in colum A i have my items but they are repeated...eg A1, A145, A166, A122 are all the same item, what i want is regardless of where i place the x each check box under coloum B either gets a X and turns green, or just turns green, i dont mind...
I dont think conditional formatting can cut this, though i may be wrong. Ive tried to use the IF function under condtional formatting but ran into problems...
If I have a cell which has been validated and has a drop down list of 4 names in it. I need it to prompt the user that when they choose there name and select, a window pop up or similar window appears asking for a 4 digit(or similar) code.
This is to stop others using peoples names against jobs they have completed so there is accountability if there are issues with information.
For each line I need it to re-prompt the user to enter the 4 digit pin so it cannot be tampered with.
Can anyone help with sending through a form and VB code possibly which I can copy into my spread sheet or a method I can use!
I want to create some VBA code, but I don't know how to do it, that allows the user to make a copy of any worksheet he/she wants within a workbook and then name the copy of the worksheet whatever he/she wants. The copied worksheet should come at the end of however many worksheets exist in the workbook.
I want to accomplish this by using input boxes for the user. I want the user to click the macro and then the input box appears asking him enter the name of the worksheet he/she wants to copy. Then I want the input box to ask the user to enter name for the new copied worksheet. That's it!
A cancel button on the input would be nice just in case the user decides not to copy any worksheet.
I want to have a code where I can have an input box accept a value of text, such as aname and then use that value throughout my code, I will also be saving a file using that name
Not sure if this can be done, but a colleague of mine is trying to record a macro which is not cell or area specific, but content specific. i.e :copy only if cell A3:A10 = "rabbit", then paste in another tab cell B7:B14.
I am trying to import txt files into Excel but instead of inmporting every row, I use the following method to select only the rows I want:
Much ADO About Text Files
However, it mentions a schema needs to be created.
For example:
[File1.txt] Col1=Name Text Col2=Age Integer [File2.txt] Col1=Name Text Col2=Age Integer
This is not a problem if I know the names of all the csv files I am importing (I can create one manually) but since I don't know how many files need to be imported, how can I create a schema for it?
Would it be possible to use VBA and create the schema dynamically?
On the "If not Isempty" line, I am trying to input a box prompt. Instead of standardizing this macro, I want the user to be able to select a dollar amount to search and place on sheet3. Right now it works fine using 1.29 as the amount. However, I have other stores where this dollar amount is different, so I want the store managers to be able to choose an amount to search, for example 1.39, 2.99 etc...
I wanted to change a column text to call intial Caps. I found this macro code on the microsoft web site
Sub Proper_Case() ' Loop to cycle through each cell in the specified range. For Each x In Range("C1:C5") ' There is not a Proper function in Visual Basic for Applications. ' So, you must use the worksheet function in the following form: x.Value = Application.Proper(x.Value) Next End Sub
I need to change the macro to have a input box (asking what column to convert text to initial CAPS)
I am trying to build a simple spreadsheet for my salesman that given a customer's zip code, it looks up the corresponding territory code for that zip code. I have conditional formatting setup so that it colors the zip code that matches the one inputted, but how do I then return the value of the corresponding territory code. The spreadsheet being searched is a simple 2 column list, with the headings: Zip Code and Territory. What's the best way to get the territory code back to my worksheet?
I am trying to automate the status date on worksheet so that if ANY data is changed/ added/ deleted within a range (cells "B6:L34"), the status date will insert today's date in Cell "S6" but if the worksheet is open and no changes occur within that range, the date remains the same. I used this code below and it is not working.
Private Sub Worksheet_Change(ByVal Target As Range) Dim R1 As Range Dim R2 As Range Dim InRange As Boolean Set R1 = Range("S6") Set R2 = Range("B6:L34")
I am trying to create a model that has a mixture of conditional formatting and data validation formulas within it, but have run into a problem in one area. Essentially, cell E10 is a Validation which pulls a drop down list of names. One of the options in this list is "Other".
I want to create a VBA code to make sure that cell E12 will get rid of any input, turn grey (color index = 15), and potentially lock (if possible!) if the "Other" is chosen by the user in cell E10. If any other value is chosen for cell E10, Cell E12 needs to remain unlocked and white (color index=2).
I usually just use macros to clean up Data from non-excel sources. So I wrote a macro to do this, but the process requires a date to be added, so attempted to do this via an input box. The input box works, but the code doesn't, clicking the button to which the macro is bound spits out an error 91 (object out of bounds?) when it hits the search function right after the inputbox code. Both bits work as intended separately, so I guess it's just some moronic formatting error on my behalf.
1) Format the cell as Text: Format->Cells->Text 2) Enter the zip code with a ' preceding the number, i.e. enter 00345 as '00345 3) Enter the zip code with the formula ="00345" ...
I have code that I just noticed will not work if user inputs a capital S. I have tried a few things but I can not seem to get it to work. Here was the original code:
I have a button set to bring up an inputbox. enter serial number.
What I need to do is enter the serial number and then have the macro look through a single column of values to match it then go to that row.
This seemed so simple I thought I could get it but alas here I sit...
I have tried to search the forum for a similar code but haven't found one that was close enough to make sense to me. I have ut together alot of individual actions, but how to get the value inputed to "search" the column and once found go to that row.
I also need to make sure it accounts for new rows being added in the future. (no set row range)
I have created an AddIn for a project I'm working on using the 'Open' command in Excel Vb. While this worked perfectly for the file I was testing it on (1,740,754 bytes) it doesn't work on a new file (121,445,125 bytes).
On testing the code, one line at a time, it gets stuck on
Line Input #1, DataLine
The files only contain one line so I suspect there is too much data for the command to handle.
I'm uploading a sample worksheet- on the scenario A tab, there is a pivot table that are pulling from the data range that is between B3:c13. I copied tab A and made tab B. However, on tab B, the pivot table is still pulling from the data range on scenario A even though I need it to be pulling from tab B.
I do not want to use dynamic ranges because the pivot table is being used to make a pivot chart. How do I get it so that on the scenario B tab the pivot table automatically pulls from the right tab?
I have also have a macro/VBA (I don't know what it is considered, I just copied code from somewhere online) that automatically refreshes all pivot tables (there are other pivot tables being used in the spreadsheet). If I did need to use vba/macro to accomplish what I need to do, where would I copy/paste it in the code I currently have (not sure if the button I have in sample will work properly)?
Sub Refresh() Dim PT As PivotTable Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets For Each PT In WS.PivotTables PT.RefreshTable Next PT Next WS End Sub
On tab B, I manually created another pivot table to show what I want the end result to look like. The pivot table between rows 22 and 24 and the corresponding pivot chart between columns E and M (lets call this pivot table/chart #1) is what I'm trying to manipulate. The pivot table between rows 26 and 28 and the corresponding pivot chart between columns o and V is what I want the end result to look like. I made pivot table/chart #2 by just manually setting the data source to be "ScenarioB!$B$3:$C$13.
However what I'm trying to do is get it so that pivot table #1 would automatically refer to ScenarioB!$B$3:$C$13 instead of ScenarioA!$B$3:$C$13. I created the tab "scenario B" but just duplicating the scenario a sheet. Thus, when I duplicate, the pivot table still incorrectly refers to data on the scenario A tab. I want it to pull from the scenario B tab.
The data range between $B$3:$C$13 automatically pulls from another sheet in my real spreadsheet.
I was using this code in a sheet that contained combo boxes and it worked great. When I redone the sheet a used data validation instead of CB it wold error out.
Code: Sub savee() Range("A4:J22").Select For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = UCase(Cell.Value) End If Next Cell Range("h4").Select ActiveWorkbook.Save End Sub
