 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / February 2006error checking
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 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 [quoted text clipped - 43 lines] > > 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 :-) 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 > Hi =?Utf-8?B?SmF5TQ==?=, > [quoted text clipped - 89 lines] > This reply is posted in the Newsgroup; please post any follow question or reply > in the newsgroup and not by e-mail :-) 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? > > > 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 [quoted text clipped - 79 lines] > > > > > > 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 :-) 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 > Hi Jay, > [quoted text clipped - 127 lines] > This reply is posted in the Newsgroup; please post any follow question or reply in > the newsgroup and not by e-mail :-) I have now got this working properly. Any help with the next bit would be greatly appreciated Thanks Jay > Cindy > Thanks for your help. the step-bystep was great (and no I didn't realise [quoted text clipped - 140 lines] > > This reply is posted in the Newsgroup; please post any follow question or reply in > > the newsgroup and not by e-mail :-) 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 :-) 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 > Hi =?Utf-8?B?SmF5TQ==?=, > [quoted text clipped - 16 lines] > This reply is posted in the Newsgroup; please post any follow question or reply > in the newsgroup and not by e-mail :-) 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 :-) 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 > Hi =?Utf-8?B?SmF5TQ==?=, > [quoted text clipped - 15 lines] > This reply is posted in the Newsgroup; please post any follow question or > reply in the newsgroup and not by e-mail :-) 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 :-)
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.