Breaking a UserForm

[Daminc]

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?

 

[Norman Jones]

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

 

[Daminc]

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

 

[kounoike]

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

 

[Daminc]

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

 

[Norman Jones]

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:

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

 

[Norman Jones]

Hi Daminic,

Your latest code worked for me.

How have you dimmed your variables?

 

[impslayer]

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

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

 

[kounoike]

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:


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

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

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


Code:

fileFilter:="Excel Files (*.xls), *.xls")

 

[impslayer]

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

 

[Norman Jones]

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!.

 

[Daminc]

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 :

 

[Norman Jones]

Hi Daminc,

Replace:

Dim savewrkbkname As String
with

Dim savewrkbkname As Variant

and replace:

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

with:

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

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

 

[Daminc]

Hi Norman, as far as I can tell it's works perfectly.

Could you explain the variable had to be changed from 'String' to
'Variant'?

-*Variant data type*
A special data type that can contain numeric, string, or date data as
well as user-defined types and the special values Empty and Null.

STRING DATA TYPE
A data type consisting of a sequence of contiguous characters that
represent the characters themselves rather than their numeric values. A
String can include letters, numbers, spaces, and punctuation. -

This doesn't explain it well (to me at least)

Also, the 'InitialFileName' confuses me a bit. I thought that my
variable

Dim wrkbkname As String
wrkbkname = Range("I15").Text

where 'I15' holds the new name of the workbook.

is 'InitialFileName' an actual (property?) of some sort that needs to
always be there?

Cheers for your help.

 

[Norman Jones]

Hi Daminc,

Could you explain the variable had to be changed from 'String' to
'Variant'?

The variable is declared as variant to allow for the boolean False if the
dialog is cancelled and to allow for a (string) entry by the user.

 

[kounoike]

kounoike skrev:

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 impslayer
Thanks explanation, and I also vote for 'not'.

i think the function GetSaveAsFilename returns value with three variation.
case1 when you put file name or select file name and push ok,
it returns that file name -type is string.
case2 when you put false as file name and push ok,
it returns "false" -type is string
case3 when you push cancel,
it returns false -type is Boolean

so, I thought a code to evaluate which is the case is something like this

s = Application.GetSaveAsFilename(wrkbkname, fileFilter:="Excel Files (*.xls),
*.xls")
If VarType(s) <> vbBoolean Then
' save file
Else
'do nothing
End If

This is what i want to say.

keizi

 

[Daminc]

Hi again,

with regards to:


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 Variant

Set rngNetwork = Range("F11")
strNetwork1 = Range("I11").Text
strNetwork2 = Range("I12").Text
strNetwork3 = Range("I13").Text
wrkbkname = Range("I15").Text
--------------------


do I have to convert

strNetwork3 = Range("I13").Text

into strNetwork3 = thisworkbook.("sheet1").Range("I13").Text?

Once I've created the add-in it's not seeing the information on the
worksheet. Instead it reads whatever worksheet is open at the time.

cheers.