error checking

[JayM]

I have created a macro which uses a userform to retrieve data to create a
folder on the network. That works fine. I have added error check to see if
the folder already exists and this works fine. The problem is getting the
macro to go back to the userform for them to amend the folder name for it to
be created.

The code follows, if anyone can help me out that would be great. I am quite
new to VBA

Private Sub cmdOK_Click()

Dim MyFilePath As String
Dim TestFolder
Dim TestAlphaFolder

If cboDept.Value = "Stourport" Then MyFilePath = "w:\data\_Clients\"
If cboDept.Value = "Worcester" Then MyFilePath = "w:\data\_Clients\"
If cboDept.Value = "Kidderminster - Business" Then _
MyFilePath = "w:\data\Business\_Clients\"
If cboDept.Value = "Kidderminster - Civil" Then _
MyFilePath = "w:\data\Business\_Clients\"
If cboDept.Value = "Kidderminster - Crime" Then _
MyFilePath = "w:\data\Business\_Clients\"
If cboDept.Value = "Kidderminster - Family" Then _
MyFilePath = "w:\data\Business\_Clients\"
If cboDept.Value = "Kidderminster - Probate" Then _
MyFilePath = "w:\data\Business\_Clients\"
If cboDept.Value = "Kidderminster - Property" Then _
MyFilePath = "w:\data\Business\_Clients\"

TestFolder = Dir(MyFilePath & txtMyFolderName) & "\"

If Len(TestFolder) = 0 Then
Handler:
MkDir MyFilePath & Left(txtMyFolderName, 1) & "\"
ClientFolders:
MkDir MyFilePath & Left(txtMyFolderName, 1) & "\" & txtMyFolderName
MkDir MyFilePath & Left(txtMyFolderName, 1) & "\" & txtMyFolderName &
"\Bills"
MkDir MyFilePath & Left(txtMyFolderName, 1) & "\" & txtMyFolderName &
"\Documents"
MkDir MyFilePath & Left(txtMyFolderName, 1) & "\" & txtMyFolderName &
"\Correspondence"
MsgBox (MyFilePath & txtMyFolderName & " created!")
Else
txtMyFolderName = InputBox("File " & txtMyFolderName & _
" already exists" & Chr(10) & Chr(10) & "Please type another folder
name:", "File Exists", txtMyFolderName)
End If

frmFolderCreation.Hide

End Sub

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?SmF5TQ==?=,

I have created a macro which uses a userform to retrieve data to create a
folder on the network. That works fine. I have added error check to see if
the folder already exists and this works fine. The problem is getting the
macro to go back to the userform for them to amend the folder name for it to
be created.

Have the cmdOK_Click do
Me.Hide

In the code that calls the form, call the procedure (function) that does the
checking. If this returns an invalid value (such as false or 0), .Show the user
form again. Build this into a loop so that it doesn't stop until there's a
valid entry. Very roughly

Sub Test()
Dim frm As UserForm1
Dim bValid As Boolean

Set frm = New UserForm1
Do Until bValid = True
frm.Show
bValid = CheckEntries(frm)
Loop
Unload frm
End Sub

Function CheckEntries(frm As UserForm) As Boolean
Dim bValid as Boolean
'Do your checking here and pass back the correct value
CheckEntries = bValid
End Function

Because the form is only Hidden, it's still available in memory and you can
query it even when the user can't see it. Only Unload from memory it once
you're finished.

The code follows, if anyone can help me out that would be great. I am quite
new to VBA

Private Sub cmdOK_Click()

Dim MyFilePath As String
Dim TestFolder
Dim TestAlphaFolder

If cboDept.Value = "Stourport" Then MyFilePath = "w:\data\_Clients\"
If cboDept.Value = "Worcester" Then MyFilePath = "w:\data\_Clients\"
If cboDept.Value = "Kidderminster - Business" Then _
MyFilePath = "w:\data\Business\_Clients\"
If cboDept.Value = "Kidderminster - Civil" Then _
MyFilePath = "w:\data\Business\_Clients\"
If cboDept.Value = "Kidderminster - Crime" Then _
MyFilePath = "w:\data\Business\_Clients\"
If cboDept.Value = "Kidderminster - Family" Then _
MyFilePath = "w:\data\Business\_Clients\"
If cboDept.Value = "Kidderminster - Probate" Then _
MyFilePath = "w:\data\Business\_Clients\"
If cboDept.Value = "Kidderminster - Property" Then _
MyFilePath = "w:\data\Business\_Clients\"

TestFolder = Dir(MyFilePath & txtMyFolderName) & "\"

If Len(TestFolder) = 0 Then
Handler:
MkDir MyFilePath & Left(txtMyFolderName, 1) & "\"
ClientFolders:
MkDir MyFilePath & Left(txtMyFolderName, 1) & "\" & txtMyFolderName
MkDir MyFilePath & Left(txtMyFolderName, 1) & "\" & txtMyFolderName &
"\Bills"
MkDir MyFilePath & Left(txtMyFolderName, 1) & "\" & txtMyFolderName &
"\Documents"
MkDir MyFilePath & Left(txtMyFolderName, 1) & "\" & txtMyFolderName &
"\Correspondence"
MsgBox (MyFilePath & txtMyFolderName & " created!")
Else
txtMyFolderName = InputBox("File " & txtMyFolderName & _
" already exists" & Chr(10) & Chr(10) & "Please type another folder
name:", "File Exists", txtMyFolderName)
End If

frmFolderCreation.Hide

End Sub

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[JayM]

Thanks for your help Cindy
i am struggling with this one though.

I have interpreted your code (probably incorrectly) as now when I click the
OK button nothing happens the userform doesn't close, no folders are created
and no error message appear. Where do I need to create the code? Do I need
to add anything else. Sorry to be such a no brainer!!!
JayM

 

[Cindy M -WordMVP-]

Hi Jay,

I have interpreted your code (probably incorrectly) as now when I click the
OK button nothing happens the userform doesn't close, no folders are created
and no error message appear. Where do I need to create the code? Do I need
to add anything else. Sorry to be such a no brainer!!!

Not a problem, and not your fault. It's always difficult to know at what level a
poster is (unless they ask in the "Beginners" group, which you may not even see
through the interface you're using).

Remote trouble-shooting is always tricky, so I hope you don't mind taking this
step-by-step. Start by copying all your code to a document or text file, just in
case something goes wrong. you don't want to lose it

In the code behind your UserForm, the _Click procedure for your OK button. Select
and comment out everything. The only active line of code should be
Me.Hide

In the procedure that displays the userForm, comment out everything and put in:

Dim frm As UserForm1 'Replace with Name of your Userform!!
Dim bValid As Boolean

Set frm = New UserForm1 'Replace with Name of your Userform!!
Do Until bValid = True
frm.Show
bValid = true
MsgBox "Hi there" '****
Loop
Unload frm


Run this procedure as a test. The UserForm should display. When you click OK it
should disappear, then the MsgBox should appear. If this works, as a further test,
substitute displaying text from a control on the UserForm for the message "Hi there"
(*****). For example:
MsgBox frm.cboDept.Value

Does all of that work, so far?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in
the newsgroup and not by e-mail

 

[JayM]

Cindy
Thanks for your help. the step-bystep was great (and no I didn't realise
there was a beginners group)
I have carried out those steps and the first part works fine. unfortunately
trying to return a value from the cboDept doesn't - would this have something
to do with the fact that I also have a textbox (txtMyFolderName) on the same
form.

I will try and figure it out for myself but would be very grateful of any
help that you can offer.

JayM

 

[JayM]

I have now got this working properly.

Any help with the next bit would be greatly appreciated

Thanks Jay

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?SmF5TQ==?=,

unfortunately
trying to return a value from the cboDept doesn't - would this have something
to do with the fact that I also have a textbox (txtMyFolderName) on the same
form.

No, that shouldn't make a difference.

Show us the macro code you've got now, and describe where the problem is
occurring? I just created a test form, with a combobox, and it's returning the
selection when I use the .Value property.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[JayM]

Cindy

I am returning a value now. The problem was that I had frm.show twice in
the code.

Can you help with the next bit (what code to put where for the error
checking. (See your first post

Jay

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?SmF5TQ==?=,

I am returning a value now. The problem was that I had frm.show twice in
the code.

Can you help with the next bit (what code to put where for the error
checking. (See your first post

I'm sorry, I don't understand where you're stuck. I don't mention any error
checking in my first post... It would probably also help for you to post the
code as you now have it.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[JayM]

Cindy
Here is my code so far. I am not sure what to put in the function part for
the error checking.
Jay

Sub frmFC()
Dim frm As frmFC
Dim bValid As Boolean

Set frm = New frmFC
Do Until bValid = True
frm.Show
bValid = CheckEntries(frm)
Loop
Unload frm
End Sub
Function CheckEntries(frm As frmFC) As Boolean
Dim bValid As Boolean
'Do error checking here



CheckEntries = bValid
End Function

 

[Cindy M -WordMVP-]

Hi Jay,

Here is my code so far. I am not sure what to put in the function part for
the error checking.

I keep going past this, hoping that my brain will figure out exactly what you
want. In your first message, you state

" I have added error check to see if the folder already exists and this works
fine."

So I can't figure out what it is you need, here? There's so much going on in
my brain, people really have to spell things out for me, I'm afraid...

You can read the information from the Userform by referencing frm.
If frm.txtInputXYZ = "abc" Then
will pick up the content of the textbox txtInputXYZ, for example. Is that
where you're stuck?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail