HELP: Run macro when Userform Close Button is clicked

MooseMoney

New Member
Joined
Jan 10, 2012
Messages
33
I need some help to run a macro when the Close Button on my userform is clicked.
I need the macro to run prior to closing the userform. This is what I have, but it
does not work;

Code:
Private Sub CloseButton_Click()
    
   Range("UserFormResults!C17").Select
   Selection.Copy
   Range("UserFormResults!C30").Select
   ActiveSheet.Paste
   ActiveCell.Replace What:="  ", Replacement:="", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
   ReplaceFormat:=False
   Application.CutCopyMode = False
   Range("Costing!A1").Select
    
Unload Me
End Sub

All help is appreciated! :)
.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
The button to close the Userform is called "CloseButton".
When I click the "CloseButton" to close the Userform, the VBA error message as per image below appears.
When debugging, the code below is highlighted in yellow.


VBAError.jpg


.
 
Upvote 0
To select a Range its worksheet must be active. But there is no need to Select. Try:

Code:
Private Sub CloseButton_Click()
   With Worksheets("UserFormResults")
      .Range("C17").Copy .Range("C30")
      .Range("C30").Replace What:="  ", Replacement:="", LookAt:=xlPart, _
         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
         ReplaceFormat:=False
   End With
   Application.CutCopyMode = False
   Unload Me
End Sub
 
Upvote 0
Excellent - it works like a charm!
You are one hell of a clever dude.
Thank you - you're my hero for today.
 
Upvote 0

Forum statistics

Threads
1,211,685
Messages
6,103,287
Members
447,853
Latest member
olddutch7

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