Activating and closing a userform in a macro

[mcmahak]

I have a userform created with the info that I need. Now i just need to be able to open it with my Auto Open macro and then close it and save the data in the combobox to a particular cell when the ok button is clicked. Anyone have code for this. I tried searching and ddin't get any returns for this topic.

Thanks,
Kevin

 

[Mudface]

To display the userform, add this to your open event code: -

UserForm1.Show

To add the data from your combobox and dismiss it, add this code to your OK button's Click event, changing the object names as appropriate: -

Private Sub OK_Click()

Range("A1") = Me.ComboBoxName.Value
Unload Me

End Sub

 

[mcmahak]

Ok I have everything working well now. THe only problem is that when I make my selection from the userform, first of all the drop down list of my combobox stays visible when I select something, and the entire user form remains visible until the entire macro finishes running. I put in a line of code on the OK_Click portion that says Userform1.hide, but that is not working apparently.

Thanks again,
Kevin

 

[mcmahak]

One more thing I can't figure out. I want to be able to end the entire macro if I click the exit button on the userform. Right now it just continues with the macro and prompts me for the rest of my input boxes, but instead I want it to end the Auto Open program so that I could edit the file without inputing any data.

Kevin

 

[Juan Pablo González]

One drastic solution would be

Private Sub ExitButton_Click()
    End
End Sub

 

[mcmahak]

One drastic solution would be

Private Sub ExitButton_Click()
    End
End Sub

I guess I worded it wrong. I do not actually have an exit button that I created, but the userform automaticaly puts an "X" in the upper right corner and when I click that I want it to end the program? Is the "X" given a name automatically or how do I refer to it?

Thanks,
Kevin

 

[Juan Pablo González]

Put this in the userform module then:

<font face=Courier New>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_QueryClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, CloseMode <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>)
    <SPAN style="color:#00007F">If</SPAN> CloseMode = 0 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">End</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[mcmahak]

That still didn't do it. Here is the first part of my Auto Open program that refers to the userform, let me know if I need to change something in it

Sub AUTO_OPEN()
    Application.ScreenUpdating = False
    ActiveWindow.Zoom = 100
    
    UserForm1.Show

    Sheets("sheet6").Select
    K = Range("A1").Value
    
    Sheets("Sheet2").Select
    Range("a2").Select
    
    A = InputBox("Enter Your Initials", "Input Initials")
    
    If (A <> "smart scope") Then
        B = InputBox("Enter Lot#", "Input Lot#")
        O = InputBox("Enter # of Open Holes", "Input # of Open Holes")
    End If
    
    With Worksheets("Sheet1").Range("A2:F2")
        .NumberFormat = "0.0000"
        .Value = .Value
    End With

Thanks again,
Kevin

 

[Juan Pablo González]

Why "it didn't do it" ? what is happening ?

 

[OdinsDream]

When you need to close the userform, try:

Unload Me

or...if you're not in the UserForm's own code section, try:

Unload UserFormName