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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
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
 

VBA_inminutes

New Member
Joined
Jul 8, 2010
Messages
12
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,274
Messages
5,485,784
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top