I'm in the process of trying to trash a userform before turning it into
a add-in.

Originally, part of my code held:


Code:
--------------------
Select Case rngNetwork
Case 1
Set wrkbkUrl = Workbooks.Open(Filename:="P:\VBA training\Excel templates for Network stats\1 Network.xls")
Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec", "TempAnalysis", "Yearly summary")).Select
Cells.Replace What:="1st Network", Replacement:=strNetwork1, LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False

Sheets("Jan").Select
Range("C3").Select

savewrkbkname = Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files (*.xls), *.xls")
ActiveWorkbook.SaveAs Filename:=savewrkbkname
--------------------


which did the job but one of the senarios I created was:

What if the user cancelled the save before the save was completed?
This highlighted a bug that saved the '1 Network.xls' as 'False'
instead of cancelling the save.

In order to try and remedy this I changed the code to:


Code:
--------------------
If (Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files (*.xls), *.xls")) Then
savewrkbkname = Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files (*.xls), *.xls")

Else: ActiveWorkbook.Close
End If
--------------------


However, this gave a 'Type Mismatch' error

Does anyone have an insight to what's going wrong please?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=507586

Hi Daminc,

Try:

Dim FName As Variant

FName = Application.GetSaveAsFilename _
(InitialFileName:=wrkbkname, _
fileFilter:="Excel Files (*.xls), *.xls")

If FName = False Then
ActiveWorkbook.Close SaveChanges:=False
Else
ActiveWorkbook.SaveAs FName
End If


---
Regards,
Norman


"Daminc" wrote in
message ...

I'm in the process of trying to trash a userform before turning it into
a add-in.

Originally, part of my code held:


Code:
--------------------
Select Case rngNetwork
Case 1
Set wrkbkUrl = Workbooks.Open(Filename:="P:\VBA training\Excel templates
for Network stats\1 Network.xls")
Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
"Sept", "Oct", "Nov", "Dec", "TempAnalysis", "Yearly summary")).Select
Cells.Replace What:="1st Network", Replacement:=strNetwork1,
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False

Sheets("Jan").Select
Range("C3").Select

savewrkbkname = Application.GetSaveAsFilename(wrkbkname,
fileFilter:="Excel Files (*.xls), *.xls")
ActiveWorkbook.SaveAs Filename:=savewrkbkname
--------------------


which did the job but one of the senarios I created was:

What if the user cancelled the save before the save was completed?
This highlighted a bug that saved the '1 Network.xls' as 'False'
instead of cancelling the save.

In order to try and remedy this I changed the code to:


Code:
--------------------
If (Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files
(*.xls), *.xls")) Then
savewrkbkname = Application.GetSaveAsFilename(wrkbkname,
fileFilter:="Excel Files (*.xls), *.xls")

Else: ActiveWorkbook.Close
End If
--------------------


However, this gave a 'Type Mismatch' error

Does anyone have an insight to what's going wrong please?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=507586

it's ridiculous assumption, but if i input false in filename box,
i think it'll make a file named as false.xls.

"Norman Jones" wrote in message
...
Hi Daminc,

Try:

Dim FName As Variant

FName = Application.GetSaveAsFilename _
(InitialFileName:=wrkbkname, _
fileFilter:="Excel Files (*.xls), *.xls")

If FName = False Then
ActiveWorkbook.Close SaveChanges:=False
Else
ActiveWorkbook.SaveAs FName
End If


---
Regards,
Norman


"Daminc" wrote in
message ...

I'm in the process of trying to trash a userform before turning it into
a add-in.

Originally, part of my code held:


Code:
--------------------
Select Case rngNetwork
Case 1
Set wrkbkUrl = Workbooks.Open(Filename:="P:\VBA training\Excel templates
for Network stats\1 Network.xls")
Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
"Sept", "Oct", "Nov", "Dec", "TempAnalysis", "Yearly summary")).Select
Cells.Replace What:="1st Network", Replacement:=strNetwork1,
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False

Sheets("Jan").Select
Range("C3").Select

savewrkbkname = Application.GetSaveAsFilename(wrkbkname,
fileFilter:="Excel Files (*.xls), *.xls")
ActiveWorkbook.SaveAs Filename:=savewrkbkname
--------------------


which did the job but one of the senarios I created was:

What if the user cancelled the save before the save was completed?
This highlighted a bug that saved the '1 Network.xls' as 'False'
instead of cancelling the save.

In order to try and remedy this I changed the code to:


Code:
--------------------
If (Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files
(*.xls), *.xls")) Then
savewrkbkname = Application.GetSaveAsFilename(wrkbkname,
fileFilter:="Excel Files (*.xls), *.xls")

Else: ActiveWorkbook.Close
End If
--------------------


However, this gave a 'Type Mismatch' error

Does anyone have an insight to what's going wrong please?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=507586

Hi Kounoike,

it's ridiculous assumption, but if i input false in filename box,
i think it'll make a file named as false.xls.

There is a diiference between the boolean False and the string "False".

If desired, the string "False" is a perfectly acceptable name for a
workbook.

The expression:

If FName = False Then

False is the boolean value; it is not a string value.

---
Regards,
Norman

"Norman Jones" wrote in message
...
Hi Kounoike,

There is a diiference between the boolean False and the string "False".

If desired, the string "False" is a perfectly acceptable name for a
workbook.

The expression:

If FName = False Then

False is the boolean value; it is not a string value.

Thank you for your reply, Norman and Sorry for misread your code.
your code can't make a file e.g false.xls

set aside above, i also think as you say at first.
but i try something like this.

s = "false"
If s = False Then
msgbox "equal"
Else
msgbox "not equal"
Endif

it returns equal.
Am i missing something?

Thanks

keizi

kounoike skrev:


s = "false"
If s = False Then
msgbox "equal"
Else
msgbox "not equal"
Endif

it returns equal.
Am i missing something?

Thanks

keizi

In this situation Excel will be able to translate between the string
"false" and the boolean value False, which might be a good thing (or
not, I vote for 'not').

But if you get another string (for example a file name) into the above
variable, it will cast the error the OP got, Type mismatch.

Is it clearer now?

/impslayer, aka Birger Johansson

Hi Norman, before I try that (there is a lot of code in total and you
suggestion would require a lot of changes) is there any reason that yo
can see that would result in my code bringing up a Type Mismatch error

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=50758

Hi Daminic,

Your latest code worked for me.

How have you dimmed your variables?


---
Regards,
Norman



"Daminc" wrote in
message ...

Hi Norman, before I try that (there is a lot of code in total and your
suggestion would require a lot of changes) is there any reason that you
can see that would result in my code bringing up a Type Mismatch error?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=507586

Hopefully this will give you a clue. I am not sure what Norman Tested that
worked:

Sub aBC()
If "C:\ABC\Myfiles.xls" Then
MsgBox "OK"
Else
MsgBox "Not OK"
End If
End Sub

The above is the situation you create if the user selects file name on the
first showing of the dialog. (this does raise a type mismatch error becuase
the IF is looking for a boolean)


so your "latest" code

If (Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files
(*.xls), *.xls")) Then
savewrkbkname = Application.GetSaveAsFilename(wrkbkname,
fileFilter:="Excel Files (*.xls), *.xls")

Else: ActiveWorkbook.Close
End If


will raise an error unless the user cancels. If it did work, then you would
show the file SaveAS dialog twice which is pretty lame as well.

--
Regards,
Tom Ogilvy



"Daminc" wrote in
message ...

Hi Norman, before I try that (there is a lot of code in total and your
suggestion would require a lot of changes) is there any reason that you
can see that would result in my code bringing up a Type Mismatch error?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=507586

Hi Tom

I am not sure what Norman Tested that worked:

My apologies, Tom and Daminc.

I re-tested my own code, using Daminc's variable names and, not
surprisingly, it worked. Then, with my mind elsewhere, I responded
nonsensically!.


---
Regards,
Norman



"Tom Ogilvy" wrote in message
...
Hopefully this will give you a clue. I am not sure what Norman Tested
that
worked:

Sub aBC()
If "C:\ABC\Myfiles.xls" Then
MsgBox "OK"
Else
MsgBox "Not OK"
End If
End Sub

The above is the situation you create if the user selects file name on the
first showing of the dialog. (this does raise a type mismatch error
becuase
the IF is looking for a boolean)


so your "latest" code

If (Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files
(*.xls), *.xls")) Then
savewrkbkname = Application.GetSaveAsFilename(wrkbkname,
fileFilter:="Excel Files (*.xls), *.xls")

Else: ActiveWorkbook.Close
End If


will raise an error unless the user cancels. If it did work, then you
would
show the file SaveAS dialog twice which is pretty lame as well.

--
Regards,
Tom Ogilvy



"Daminc" wrote in
message ...

Hi Norman, before I try that (there is a lot of code in total and your
suggestion would require a lot of changes) is there any reason that you
can see that would result in my code bringing up a Type Mismatch error?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread:
http://www.excelforum.com/showthread...hreadid=507586

Thanks for your patience :)

My Dim list is:


Code
-------------------
Dim rngNetwork As Range
Dim strNetwork1 As String
Dim strNetwork2 As String
Dim strNetwork3 As String
Dim wrkbkUrl As Workbook
Dim wrkbkname As String
Dim sPath As String
Dim savewrkbkname As String

Set rngNetwork = Range("F11")
strNetwork1 = Range("I11").Text
strNetwork2 = Range("I12").Text
strNetwork3 = Range("I13").Text
wrkbkname = Range("I15").Text
sPath = "P:\VBA training\Excel templates for Network stats\
-------------------


One of the main reasons this code may not be very efficient is that I'
still training and this project has grown somewhat.

Originally I had:

Code
-------------------
savewrkbkname = Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files (*.xls), *.xls")
ActiveWorkbook.SaveAs Filename:=savewrkbknam
-------------------

which worked fine until I tried to anticipate actions from a user tha
might scr*w it up. Canceling the save once the saveas dialog box ha
been activated is what brought this attempt at code modification.

The new code allowed me to cancel ok but messed up the save itself.

I shall try and work out how I can use your suggestion to solve thi
problem.

Thanks again for your help :

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=50758

I've altered the code again but I'm still getting the error:


Code
-------------------
If (Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files (*.xls), *.xls")) Then
savewrkbkname = Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files (*.xls), *.xls")
ActiveWorkbook.SaveAs Filename:=savewrkbkname

Else:
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End I
-------------------


The first line is highlighted when I try to 'debug'

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=50758

This whole approach is flawed. Why not use Norman's suggestiong.

--
Regards,
Tom Ogilvy


"Daminc" wrote in
message ...

I've altered the code again but I'm still getting the error:


Code:
--------------------
If (Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel
Files (*.xls), *.xls")) Then
savewrkbkname = Application.GetSaveAsFilename(wrkbkname,
fileFilter:="Excel Files (*.xls), *.xls")
ActiveWorkbook.SaveAs Filename:=savewrkbkname

Else:
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End If
--------------------


The first line is highlighted when I try to 'debug'.


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=507586

Daminc skrev:

If (Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files (*.xls), *.xls")) Then
savewrkbkname = Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files (*.xls), *.xls")

Else: ActiveWorkbook.Close
End If
--------------------


However, this gave a 'Type Mismatch' error

Isn't it because GetSaveAsFilename returns EITHER the boolean value
false, or the string value you've given in the message box? (And you've
used Dim on the variables.) Try to search (Google) for
GetSaveAsFilename in this news group.

/impslayer, aka Birger Johansson