Userform - El Loco

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
Hello

Had some handy code working, and then to make things neat and tidy and easier to give to users across the network, I've moved it all into a userform.

Upon clicking the button, it should open an inputbox, take the answer as a string, and use it to find a folder of that name where some modules are kept to import to the Personal.xls file.

Problem is that stepping through the code, it seems to think that when I enter "Hamburger", that the string "Hamburger" = vbCancel is TRUE. Which, last time I checked, it effin'-isn't.

Last line of the code below

Code:
Private Sub CommandButton2_Click()

On Error Resume Next
   Application.VBE.VBProjects(1).References.AddFromGuid _
        GUID:="{0002E157-0000-0000-C000-000000000046}", _
        Major:=5, Minor:=3
Err.Clear

Call MYimport

End Sub

Sub MYimport()
Application.ScreenUpdating = False
Dim DAddress As String
Dim BAddress As String
Dim x As Integer
'Base address for all the macro sub-folders
BAddress = "\\Server\Schedules\MSExcel\Company Schedule\macros"

'Activates the Microsoft Extensibility reference pack
On Error Resume Next
   Application.VBE.VBProjects(1).References.AddFromGuid _
        GUID:="{0002E157-0000-0000-C000-000000000046}", _
        Major:=5, Minor:=3
Err.Clear
        
'Resulting string from the following input box will provide the basis for removing old modules, fetching the new ones, and adding
'macros to the toolbar. The naming convention is strict, but simple. Whatever 4 characters are used, they must
Dim answer As String
answer = InputBox("Please enter the 4 character acronym for your dept" & Chr(10) & _
            "If your dept's acronym is fewer than 4 characters, make up the difference with " & Chr(10) & _
            "underscores ('_')." & Chr(10) & _
            "(e.g., If Show acronym is 'SP', enter 'SP__')")

If answer = "CS__" Then
    Run "CSimports"
    Application.ScreenUpdating = True
    Exit Sub
ElseIf answer = "GENE" Then
    Run "GENEimports"
    Application.ScreenUpdating = True
    Exit Sub
ElseIf answer = vbCancel Then
    Exit Sub
End If


if you enter the string as "CS__" it works, if you enter "GENE", it works, but enter anything else, and it seems to think that vbCancel = True.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Oh and before I even get to the input box - vbCancel = 2

I tried a line above it to say vbCancel = 0, but that just gave me an error
 
Upvote 0
Hey,

You might want to use a Select case over here :

Select Case answer
case "CS__"
Run "CSImports"
Application.ScreenUpdating = True
Exit Sub
case "GENE"
Run "GENEImports"
Application.ScreenUpdating = True
Exit Sub
case vbCancel
ExitSub
End Select
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top