I'm trying to use the value of a combo box as the filename when saving as. The code i have so far is:
Dim fName As String
fName = FileNameTXBX.Value
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlCSV, _
CreateBackup:=False
I am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?
View 3 Replies View RelatedAm trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.
if column a = bears and column b = colours of bears then
when I select bears in combobox one, combobox 2 would populate with colors of bear.
I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.
I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.
I need to populate two combo boxes from excel sheet, the data will be like below:
Column A Column B
A 1
A 2
A 3
A 4
A 5
B 100
B 101
B 102
So from the above data, one combo box should hold unique values A & B.
On selecting a value from the 1st combo box A or B, respective values should be populated in 2nd combo box.
So the data should be like below:
If A is selected in the 1st combo box, then 2nd combo box should only show the values 1,2,3,4 & 5.
If B is selected in the 1st combo box, then 2nd combo box should only show the values 100,101 & 102.
Friends I need it in a macro and one important point is, this is dynamic and it is not static and the data can be more.
When I make a comboBox selection from the dropdown menu, the menu stays down until the last statement of the comboBox code is finished. How do I make the dropdown disappear immediately after the selection is made?
View 9 Replies View Relatedtry and achive is when a user selects a item from the 1st Combobox the 2nd Combobox is the populated with the cell that is to the right of the selected item.
for Example if a user selects AAB from combobox1,, Combobox2 should populate with Belly.
Maby using combobox1_Exit for the excercise.
I created a UserForm then linked ComboBox1 to range A2:A, TextBox2 to range E2:E, and ComboBox3 to range M2:M of the same worksheet, named Sheet3. The row contents in Column A, Column E, and Column M are associated. Therefore, when the UserForm is active I want to be able to select a row from Column A in ComboBox1 and have the UserForm pull the contents from the same row of Column E into TextBox2, and Column M into ComboBox3. Here is what I have so far, but its not quite doing it.
Code:
Private Sub UserForm_Initialize()
Sheets("Sheet3").Activate
Dim ColARange As Range
[Code].....
I have a table, headers "FirstName" and "SurName".
Further a Userform with 2 Comboboxes "FirstName" and "SurName"
I'd like to choose the FirstName (say Jack) in the "FirstName" combobox, and based on that get the choice of the Surnames of all my Jacks in the "SurName" combobox.
Actually my sheet has much more fields and comboboxes, but i think my problem is just that I do not find a way to populate them dynamically.
How can i Loop through a combobox's values and compare to a string value and then set the listindex of the combobox to that value?
View 5 Replies View RelatedI have created a userform in which there are numerous Text and Combo boxes. In ComboBox 1 I have, in effect, a list of named ranges in a spreadsheet.
I want the RowSource for ComboBox 2 to equal the item selected (named range) in ComboBox 1.
VB:
Private Sub UserFormcriteria_Click()
Set ComboBox2.RowSource = ComboBox1.Value
End Sub
I want to fill two Combobox (cmb 2 and 3) dependent on what the user has chosen in a first ComboBox (cmb1).
The first combobox ist based on on the first sheet, and the second and third combobox shall be filled with values from other worksheet corresponding on combobox 1 selection.
I attached a xls to this post to explain better.
I have a userform with a combobox that has three items to choose from: Blue, Red, and Yellow. When a user selects one of those options, I would like another combobox to appear on my userform with a specific list for that option.
View 2 Replies View RelatedI'm trying to make a userform that has 2 combo boxes. I have just 3 columns right now.
Procedure GrpADA-QSI DescProcedure GrpAnesthesia And
Drugs9210-LOCAL ANESTH/NO SURGAnesthesia And DrugsAnesthesia And
Drugs9212-TRIGEM BLOCK ANESTHCrowns And BridgeAnesthesia And
Drugs9215-LOCAL ANESTHESIADenturesAnesthesia And
[Code] ........
I copied and pasted Column A into Column C and then removed duplicates. I named Column C 'ValList' and placed it in the RowSource for ComboBox1. What I now want is for ComboBox2 to populate based on my selection in ComboBox1. There are no duplicates in Column B. Duplicates are in Column A.
I also named Column A 'Proc_Grp' and Column B 'ADA_QSI_Desc'
For the properties in Combobox2, I left the RowSource empty. (that's correct right?) Because there's going to be a code that links Combobox2 to Combobox1... I think...
is it possible to perform calculation such as sum,multiply value of 2 combobox and get it shown at 3rd combobox/label at userform initialize stage?
I have a filename 'filename-12-14-2014' where i need to extract date information from the filename and insert a new heading in the file 'date' in the first column and write the extracted date till the end (until data exists). I can write the new column name but i could not write date in all the fields. code i wrote is below
this is for extracting date from the filename. the 4th lines throws me the error?
(1) LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
(2) With Range("A2:A" & LastRow)
(3) x = Split(s, "- ", 2)
(4) .Value = Left(Mid(x(1), InStr(x(1), "-") + 1), InStr(Mid(x(1), InStr(x(1), "-") + 1), ".") - 1)
(5) .NumberFormat = "mm/dd/yy"
for adding the date in the first column. This works fine
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
s = ActiveWorkbook.Name
Range("A2").Select
I have excelfilename starts like abc - usa sales- 1-1-2014. have to extract usa from the filename and insert as a column in excel with columnname 'country'.
View 1 Replies View RelatedI picked up a bit of code to create a CSV with a | delimiter. so that I could save the file with a date added onto the filename. i.e. myfile060309.csv.
View 4 Replies View RelatedI have a problem with the following code
View 2 Replies View RelatedI have filename as filenamex - filenamey-11-13-2013. I want to get only the date from the filename.
View 3 Replies View RelatedI need to add a date to the existing filename it is being saved as. Currenntly the filename is saved as follows:
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
y = Cells(a, 12)
Workbooks.Add ("C:Documents and SettingsDesktopTemplate.xlsx")
ActiveWorkbook.SaveAs FileName:=y
Next a
I want the filename to stay the same as above but add "-r-"a number entered into a textbox representing the revision and then the date the file was saved in the following format dd/mm/yy.
The form containing the text box has been scripted and works fine. The string entered in the textbox currently resides in the Active sheet being saved in cell "E4".
Below is a completed Filename. Note the RED is what i need to add.
2138-035-PW-COM01-RCS-001-r-1-28/Aug/09
Is there a way to take filename and place value in a cell
View 9 Replies View RelatedI've made a simple filename retriever. Click on the button to list the filenames under the specified path. I would like it to go deeper into subfolders. At the moment it only drills down one level. Need to make it able to drill down to all the levels present and list out the filenames.
View 4 Replies View RelatedI am trying to use the formula below to get the filename of the spreadsheet, it seems to work fine but when you open a second sheet the first sheet will pick up the name of the second. =MID(CELL("filename"), FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1). These spreadsheets will then be uploaded to another application and having 2 with the same name could cause issues. Obviously the answer is to only open one at a time, but this is always subject to human error!
View 3 Replies View RelatedI have a userform where I have 2 comboboxes. The first combobox shows the the first column (only 1 of each) and the second comboBox shows me the secondary list that correlates to the valuse in the first from column B. Now I have a text box that I am trying to get the value from column C depending on what I have in the first 2 comboboxes. What is the easiest way to do it? This is all in VB since it is a UserForm, and using Vlookup seems to be too many lines if I go that route. Is there a way to use Index and Match in VB where it would be more efficient? I attached just a sample of how the data would be layed out in the Excel sheet.
View 4 Replies View RelatedEvery week we produce a report that has the filename: Master_BER_Pending Revokes Report_YYYY-MM-DD.xlsm
Some of the information from this workbook needs to be pasted into a new tab on a different workbook. The new tabs naming convention is simply "DD MM". Currently I've got a command prop that asks the user to enter it in manually. See below
VB:
Dim newsh As String
newsh = InputBox(Prompt:="Enter new tab name", Title:="Tab Name", Default:="e.g. '05 05'")
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
ActiveSheet.Name = newsh
The user gets the information for the month and day from the filename of the report that was produced. What I need is the DD and MM to be extracted from the filename and used to name the new sheet.
CELL("filename") gives you the name of the current workbook but if you switch to another workbook and then come back, the name has switched to the other workbook. You have to refresh to make the name switch back to that of the workbook you used the formula in.
What alternatives exist, short of writing some VBA, which is an option, to fetching the name of the workbook via a formula and having it remain the name of that workbook when you go away and come back again?
I have a folder full of sales reports, all of the files are given a consistant format e.g. 09_wk50. I have a macro that do various calculations in each book. I now need a calculation based on the report date.
I am able to get the file name in the sheet so that I am just left with the value above (09_wk50) but I need to be able to get an actual date from that value.
I need to be able to get the monday of the week given. for the example given i would want the date 13/04/2009 because of week 50 in 2009, the 13th is the Monday.
I'm working on a project where I need to populate a column with vlookups taken from different workbooks, I have for the most part got it working. However currently, everytime it populates a cell it prompts the user to select the sheet from the workbook as there are two. The sheet name is always the same so I tried to add it into the code so it would avoid having to repeatedly click okay while it populated the table.
This is the original code where it asks for the user to select the sheet every time.
[Code].....
This is what is produced when it is run and the user selects the worksheet each time (which works perfectly fine):
[Code] ...........
I tried to enter the Full Costs sheet name into the code like so:
[Code] ......
However this produces the following:
[Code] .....
The issue I have found is that the square brackets that are around the filename are generated automatically, they aren’t in the actual filename and I haven’t put them in, so I am struggling to work out how to add the sheet name in where it wont be included within the square brackets, as that is what's breaking the lookup.
I would like to modify the below so that the contents of cell D2 of the current file becomes the file name.
View 2 Replies View RelatedI want to use this code to display only the filename + extension in the TextBox1. It currently shows the full path
View 8 Replies View Related