Use GetSaveAsFilename Correctly

Aug 14, 2007

how to use some form of the saveas dialog within excel without quotes appearing in the filename that is passed to the dialog.

For example:

ChDir ("J:TestFolder")

x = Application.GetSaveAsFilename("Test.xls", "Excel files (*.xl*),*.xl*", 1, "Custom Dialog Title")

The above code displays the saveas dialog form but has "Test.xls" surrounded by quotes. Normally, there would be no quotes around the specified filename.

Is there another way I can do this or will I just have to live with it?

View 3 Replies


ADVERTISEMENT

GetSaveAsFilename Date Format

Nov 1, 2007

Using the GetSaveAsFilename function outputs dates in the format mm/dd/yyyy, whereas in Australia we us dd/mm/yyyy.

Is there a way of modifying this, OR is there another method to VBA export a spreadsheet to a tab-delimited file?

This is the Private Sub ExportMe_Click()
Dim TextFilename As Variant
TextFilename = Application.GetSaveAsFilename(InitialFileName:="TimeSheet" & Year(Date) & "-" & Month(Date) & "-" & Day(Date), _
filefilter:="Text Files (*.txt), *.txt", _
Title:="Change the name for the text file and a folder in which to save the text file.")
If TextFilename False Then
ThisWorkbook.ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=TextFilename, FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.SaveAs
ActiveWorkbook.Close SaveChanges:=False
End If
End Sub

View 9 Replies View Related

How To Correctly Create OR Function

Mar 3, 2014

I should i make this formula

A customer is eligible for a discount if the customer’s 2009 sales are greater than or equal to 200000 OR if the customers First Order was placed in 2012. " If its true it would be yes and if false no"

So far i have in the IF function
=IF([@[2009 sales]]>= 200000 ,"Y","N")

How should i insert if the customers OR First Order was placed in 2012?

View 2 Replies View Related

Cell's Are Not Lining Up Correctly

Jan 24, 2007

I have used the Excel program for a number of years to print out "Jukebox Title Strips" for my Jukebox. I have used the same "formula" for cell height, width, etc. during that time.

The outline of the title strips are preprinted and perforated on an 8.5" x 11" sheet. I recently tried to make some new strips and the printing does not line up correctly as it always had done in the past. Now, the titles are not high enough on the top portion of the sheet, and not low enough on the bottom portion of the sheet. So, it is not that I could just raise everything up or lower everything down to line up correctly... it now has to be changed in both directions. Of the 20 title strips on the sheet, the only ones that line up correctly are the two that are halfway down.

I thought that somehow the file had become corrupted, so I made a new template, but I still have the same problem.

Are there any suggestions of what I could do?

In the links below it shows an example of a Title Strip page.

View 14 Replies View Related

Numbers Not Sorting Correctly

Jul 31, 2013

I have three columns expressing death dates (date, month, year). The months and dates are finally sorting correctly within the same year, but I have it set to sort by year first.

The column starts off fine with 1860, goes up to 2013, then starts back at 1884 and goes back up to 2009. I can't find any difference between the two groups: there are some of the same years represented, there are blank month and date cells in both. I have a worksheet with birth dates that sorted fine; the only difference I can find is that birth dates only go up to 1997, but the column is formatted as General, so the turn of the century shouldn't be a problem.

I've tried highlighting the whole thing before sorting as well as selecting one cell as usual. I've also tried formatting the column as Text. I can't think of anything else.

View 14 Replies View Related

UserForm Not Displaying Correctly

Jan 29, 2009

I have built a UserForm (Excel 2003) that looks up a Row dependant on Criteria and displays the values on the User Form.

View 9 Replies View Related

Focus Not Setting Correctly

Jul 21, 2009

I have a userform with several inputs which are validated, and should the input be invalid an ErrorProcedure is run. This basically displays a message telling the user what is wrong, and it should also be highlighting the text in the relevant field, but for some reason it is not doing that.

View 6 Replies View Related

How To Link Two ComboBoxes Correctly

Sep 27, 2012

Linking two comboboxes. Actually My department consists of 4 divisions and each division contains 3 sections, that is beside Department manager staff and planning group.

In the attached workbook:

- Combobox1 contains All divisions, Department manager staff and planning group)
- Combobox2 contains all sections of the selected division in Combobox1.

What I want to do is the following:

In Combox1, I want to hide all records not related to the selected division.
In Combox1, I want to hide all records not related to the selected section.

Remarks:

I depend for filtering on the org. code for each division and section.

Fifth and Sixth digits of the first Division's Org. Code are 21

Fifth and Sixth digits of the second Division's Org. Code are 22

Fifth and Sixth digits of the third Division's Org. Code are 23

Fifth and Sixth digits of the third Division's Org. Code are 24

First section's org. code for each division ends with 11

Second Section's org. code for each division ends with 21

Third Section's org. code for each division ends with 31

I have created the following VBA codes to reach the said purposes, the first three ptions in Combobox1 work just fine, but the other options sometimes work fine and other times don't!

Moreover, Combobox 2 is not working at all:

Code:
Private Sub ComboBox1_Change()
On Error Resume Next
Application.ScreenUpdating = False
Select Case ComboBox1
Case Is = [AC4].Value

[Code] ........

View 1 Replies View Related

Unable To Sort Correctly

Oct 23, 2007

I have a column with a value resulting from a fairly complex formula:

I will not include the formula, but basically what it does is a number of calculations including subtracting dates, that is

TODAY'S DATE - A GIVEN DATE (provided in a different cell)

That means that, either manually or automatically, those values are constantly recalculating (with every second that goes by, that value changes).

And the number of values/cells to update is not few: about 1100.

Here's the problem.

When I press SORT, Excel seems to be absolutely incapable of sorting that list correctly. The values are completely and randomly out of order.

I wonder if it could be due to the fact that given that those values would be constantly changing, there would be a conflict with the commands

sort
update

View 9 Replies View Related

Not Referencing Cells Correctly

Dec 8, 2008

I'm trying to simply reference a cell from another tab in the same workbook. In other words I press "=" and then point to the cell in another tab and I would expect to see that value - however I don't. What I see, as an example, is: ='Nav&SPDiary'!B24 instead of the cell value (which I know exists because I've just pointed to it.

Anyone have ANY idea why all of a sudden it's started to do this? The most annoying thing is it worked fine before and now it doesn't do what I want...

View 9 Replies View Related

Textbox Not Printing Correctly

Jun 11, 2009

I've got a text box with some commentary in the middle of a spreadsheet. The text looks fine on screen, but when I print, it chops the end off some of the words. Is there a way round this?

View 9 Replies View Related

Combobox Not Populating Correctly

Sep 12, 2006

I have a userform with a combo box that is populated from a list in a sheet called Clients. The userform is in the same workbook as the Clients sheet. This workbook called "Client List.xls" and is set to be hidden from view. I've created a menu bar icon that loads the "Client List.xls" workbook, and on open runs the initialize procedure. The problem seems to be caused by the "Clients List.xls" being not the active workbook. Is this the case? Is there a way around it? If I unhide the workbook and make it the active workbook, the code works great.

Private Sub UserForm_Initialize()
Dim rngClients As Range
Dim wsSheet As Worksheet
Set wsSheet = Workbooks("Client List.xls").Sheets("Clients")
With wsSheet
.Range("a65536").End(xlUp).Select
.Range(Selection, "A1").Select
End With
Set rngClients = Selection
With cmbClients
.RowSource = rngClients.Address
.ListIndex = 0
End With

View 3 Replies View Related

Variable Not Assigning Correctly

Oct 31, 2006

The relevant code is below. I can post it all if necessary -- it's about 30 lines though. Can anyone recognize what the problem might be?

MsgBox "vSh " & vSh & " vOp " & vOp & "vCash " & vCash
vCash = vSh * vOp
MsgBox "vCash " & vCash

The MsgBox's are merely for debugging. When the code above runs, the first MsgBox I receive states similar to:
"vSh 14.238964 vOp 45.23 vCash 1000"
The second MsgBox, from after the multiplication, states:
"vCash 1000"

For some reason the vCash variable just will not set. At the beginning of the code, I declared each variable as Dim var, meaning as a variant, and whenever I debug by halting during a MsgBox display, each of the variables appear to be the same types: Variant/Double.

View 6 Replies View Related

Command Button Not Working Correctly?

May 1, 2013

I have a command button on my form that unloads it when i am finished. But i have to press it 20 times before it closes. The only line i have in the code is "UnLoad Me"

View 7 Replies View Related

Can't Format Correctly A Pivot Table

Feb 6, 2014

formatting the sub-row header of the table also changes one of the columns.

View 6 Replies View Related

Time Formula Not Calculating Correctly?

Feb 19, 2014

In cell BQ65, I have 7:00 pm. Next to that, in cell BR65, I have 6:00 am. This should total 11.00 hours, yet with the formula I have in cell BS65, the result is showing up as 13.00 hours, so I'm ending up with two extra hours, which is not good.

Here's the formula I have in cell BS65:

=IF(ABS((((BR65-BQ65)*1440)/60))=0,"",ABS(((BR65-BQ65)*1440)/60))

View 3 Replies View Related

ComboBox On Form Does Not Display Correctly?

Apr 23, 2014

I created a data entry form that contains a ComboBox. The ComboBox is based on a range that has three columns and many rows. The purpose is to have the user select an item from the first column, and the other two columns are automatically filled in on the table.

The form works and the data is transferred to the table as it should. The issue is that when you select the ComboBox, it displays the data from all three columns wrapped in two columns. Some of the data is repeated and it looks confusing. How do I have the drop down show only the data in the first column?

View 1 Replies View Related

Filtering By Dates Not Working Correctly?

Jun 26, 2014

[Code] ....

In column F I have a list of dates (dd/mm/yyyy).

I can manually filter dates ok, but want to set it to part of code to use automation for a report.

The code when run, finds nothing, where it should. I tried using [CDate] also but also same result.

View 3 Replies View Related

Range Not Appearing Correctly In Locals

Feb 21, 2014

I have the following code:

[Code] ........

HierLastRow shows as 165 in Locals which is correct. When I look at HierUID in Locals the Range is B2:B2 and not B2:B165. How can I make the Range set correctly?

View 2 Replies View Related

Cell Not Copy Correctly To Second Workbook

Mar 20, 2014

I'm trying to copy a cell from one workbook and paste it into another workbook. Both workbooks are open so that is not the problem. The problem I'm running into is that in the first workbook where I'm getting the information the cell is tied to another. And the cell it gets the information from is running a COUNT function. So the problem I run into is that when I go to copy the cell and paste it into the new workbook I'm getting 0 instead of the correct number found on workboook1.

View 1 Replies View Related

Command Button Not Populating Correctly

Jan 16, 2009

I am using Office 2003. I found some code in Ozgrid and have been using for one application. I am building another application and having problems with the command buttons operating properly. I am attaching a sample worksheet. Just click on the "click here to search and add button" It will open a userform. You will see one side that has the following:

1st Pickup
2nd Pickup
3rd Pickup
This populates itself by looking at the student row and that specific column. On the other side you have:

1
2
3
and Add Yes

This is where you put a yes and have it add to the proper column for that student. This is where I am having problems. I cannot seem to get it right.

View 4 Replies View Related

Check Whether All The Formulas Are Dragged Down Correctly

Nov 16, 2009

How to create a macro tool to check whether all the formulas are dragged down correctly? Because someone may accidentally make some changes to some cells.

For example,
A1=IF(NOT($B1), -SUMIF(PCAInfo'!$C1, AQ$10:AQ$150),0)
A2=IF(NOT($B2), -SUMIF(PCAInfo'!$C2, AQ$10:AQ$150),0)
A3=IF(NOT($B4), -SUMIF(PCAInfo'!$C1, AQ$10:AQ$150),0)
A4=IF(NOT($B4), B4-SUMIF(PCAInfo'!$C4, AQ$10:AQ$150),0)
The code could test out
A2 is correct,
A3 and A4 is not correct.

View 2 Replies View Related

UserForm - Component Not Correctly Registered

Feb 19, 2012

When I try to open an Excel-file from my employer with Excel UserForms I recieve following error messages

Code:
Component not correctly registered
and
Code:
Unexpected error (336)

I can see the code behind the UserForm, but not the UserForm. I've made the same mistake myself last time I created an UserForm, but I don't know what to do different.

View 2 Replies View Related

Countifs Function Not Summing Correctly

Jul 13, 2012

I have yet run into another road block, which is stated below.

=SUM(COUNTIFS(AF20:AF111,{"*CoSale**","*Reo**"},AL20:AL111,{"Seller - Core","Seller - Non Core"}))

I can not get that formula to sum correctly. I can get them to sum if I seperate, but not with the countifs formula above. I am stuck at this point. So, any insight would be great. It is supposed to add up to 13, but I am getting a sum of 7.

REO-Svcr
Seller - Core
CoSale-Pri
Seller - Core

[Code]....

View 3 Replies View Related

Dates In Chart Not Display Correctly

Mar 5, 2013

I have data that shows titles as 2-Feb, 2- Mar; which is really typed in as 2/5/2012 and 3/15/2012. My chart when graphed keeps showing the X-axis as 1-FEB and 1-MAR. How can I get the graph to show the x-axis titleas as 2-Feb and 2-Mar?

View 3 Replies View Related

Looping Code Not Ending Correctly

Dec 29, 2013

I have the following code that I use to create individual client files from one master spreadsheet. My problem is that it always creates and saves a blank file with only the 'rn' value in the file name. It seems that it is looping one extra time when the 'rng.value' list ends.

Sub MakeFiles()
Dim wb As Workbook
Dim ws As Worksheet
Dim criteriaRng As Range, usedRng As Range, rng As Range
Dim lh As String, ch As String, rh As String
Dim rn As String

[code]....

View 1 Replies View Related

Correctly Reference Worksheet In VBA Formula

May 5, 2014

I am trying to use a match function to determine which row information resides on so that I can update other information on that row but I am having problems setting up the match function.

The information is obtained from a combobox on a user form which identifies the correct address and then additional information is added to other fields on that line in the spreadsheet. PropertyListAll is a dynamic named range looking at the first column in the spreadsheet. the data starts in row 11.

My code is as follows, but I am getting error message 438. I am assuming I am referencing the workbook and named range incorrectly but I have tried various different combination all to no avail.

Code:
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Team Vals - Take ons")
rownumber = WorksheetFunction.Match(cboProperty.Value, ws & "!PropertyListAll", 0)
Sheet1.Range("tblTakeons").cells(rownumber, 2).Value = txtDate.Value

View 6 Replies View Related

Time Format Not Reading Correctly

Dec 24, 2009

I have a time calculation in E2 that is a result of a number multiplied by at time the results are nearly always over 1,000 hrs
I have the below formula to display the results in the format of Hrs and Mins but it isn't displaying correctly ie the number in E2 is 1666:40 so it should read 1666 Hrs 40 Mins but its displaying as 10 Hrs 40 Mins

=HOUR(E2)&" Hr"&IF(HOUR(E2)1,"s "," ")&MINUTE(E2)&" Min"&IF(MINUTE(E2)1,"s "," ")

View 9 Replies View Related

Auto Sort Not Working Correctly

May 12, 2006

I'm try to get the pos. placing in a auto sorted table to remain in place
i.e. 1st next to the top position. 2nd next tothe next position and so on down. but it keeps getting it wrong! I've tried amending the macro serval times but it makes things worse! Sorry can't attach the file, too big for this forum!!!!

View 2 Replies View Related

SortSheets Macro For Statement Does Not Run Correctly

Jul 15, 2006

i have tried 2 different ways of sorting my worksheets in alphabetical order. Each time i try these it does something to all the sheets where it's not letting my For statement to run correctly. It was running correctly before I added the Sort Sheets macro. My workbook has a bunch of sheets for every agent and one summary sheet. The for statement looks in A1 for the condition. the condition is in every sheet except for the summary sheet.

Here is my For statement. Its was looking in A1 for the criteria before and working fine but for some reason it now is finding the criteria in the "summary" sheet when there criteria is not there?!?!

Sub run_it()
If Range("A8") <> "" Then
Range("A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
End If
Dim month As Integer
Dim month1 As String
month = InputBox("Enter the MONTH (Number) you are reporting")
If month = 1 Then
month1 = "January"
ElseIf month = 2 Then
month1 = "February"
ElseIf month = 3 Then .............

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved