Closing Userform with another Macro??

buzz71023

Active Member
Joined
May 29, 2011
Messages
295
Office Version
  1. 2016
Platform
  1. Windows
I need some help here. Hopefully it won’t be too confusing.

I have the below code that code is supposed to be lookingthrough the range, setting a couple of variable, and then deleting the last usedcell in a range but the problem is it keeps bringing up a userform (frmTime1)while the code is running and I do not want it to.

The problem is within this range, when any of the cells areselected by the user (using Worksheet_SelectionChange), I have a userform that is supposed to pop up to allow theuser to enter their data.

Is there a way to declare these variable which would be thelast data in range(s) DateCol and EndCol and then would also find the last data inrange StartCol and clear contents WITHOUT bringing up the userform?

-OR-


Is there a way to close the userform that pops up after ispops up with in the code?

Code:
[FONT=Calibri][SIZE=3][COLOR=#000000]Public globDate As Double, globTime As Double[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]Sub Button10_Click()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
'get the last end time for nextjob start time[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
Dim xLastRow As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]          WithApplication.ActiveSheet.Range("EndTimeCol")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]           xLastRow =.Cells(.Rows.Count, "a").End(xlUp).Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Unload frmTime1[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    globTime =ActiveCell.Value[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]'get the last end date for the next job start date.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
Dim xLastRow2 As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]          WithApplication.ActiveSheet.Range("DateCol")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            xLastRow2 =.Cells(.Rows.Count, "a").End(xlUp).Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    globDate =ActiveCell.Value[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Unload frmTime1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] 
[FONT=Calibri][SIZE=3][COLOR=#000000]'clear the last copied start time[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Dim xLastRow3 As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    
WithApplication.ActiveSheet.Range("StartCol")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        xLastRow3 =.Cells(.Rows.Count, "a").End(xlUp).MergeArea.ClearContents[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]   End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [FONT=Calibri][SIZE=3][COLOR=#000000] Unload frmTime1[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I think this should do it..


Code:
Public doRun As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If doRun Then
        'Your existing SelectionChange code goes here...
        '
        '
    End If
End Sub


Sub Button10_Click()
    doRun = False
    'get the last end time for nextjob start time
    ' Your existing code Goes here

    doRun = True 'This needs to happen at the end of your button click event
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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