Copy paste question for VBA

praztitute

New Member
Joined
Sep 6, 2006
Messages
4
1)
When the macro copyies a section of text from one spreadsheet into another there is a prompt to keep the data copied in memory. I want to disable this feature/prompt. How do I do that?

2)
When using a form my macro takes about 30 seconds to run, how do I disable the buttons while the macro is running, and then enable them when the macro is completed. Each button is assgined a different macro.

Thanks!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Can you post your code?

Generally you can suppress alerts by adding this to the code.
Code:
Application.DisplayAlerts = False
And to turn them on once the code has run.
Code:
Application.DisplayAlerts = True
 

praztitute

New Member
Joined
Sep 6, 2006
Messages
4
Here is one of the buttons. If you want me to post all the code for all the buttons let me know, it will take a lot of space. This button takes about 20-30 seconds to run (as it connects to a server and retrieves files). I want to disable all the other buttons (four) in my form while this is running and then enable them after the macro has finished.

Thanks!

Private Sub CommandButton1_Click()
ip = InputBox("Please enter IP")
user_name = InputBox("Please enter User Name")
pwd = InputBox("Please enter Password")

line_num = 0
Count = 0

Open "C:\TEST.bat" For Output As #1
Print #1, "ftp -s:C:\TEST.txt " & ip & " > C:\log.txt"
Close #1

Open "C:\TEST.txt" For Output As #2
Print #2, user_name
Print #2, pwd
Print #2, "cd /space/dbbackup/Daily/"
Print #2, "ls"
Print #2, "bye"
Close #2

retval = Shell("C:\TEST.bat", 1)

Application.Wait Now + TimeValue("00:00:05")

Open "C:\log.txt" For Input As #3

Do While Not EOF(3)
Line Input #3, textline
line_num = line_num + 1
Loop

Close #3

Application.Wait Now + TimeValue("00:00:05")

Open "C:\log.txt" For Input As #4

For Count = 0 To line_num - 7
Line Input #4, textline1
Next Count

Close #4

Application.Wait Now + TimeValue("00:00:05")

Open "C:\TEST1.txt" For Output As #6
Print #6, user_name
Print #6, pwd
Print #6, "cd /space/dbbackup/Daily/" & textline1 & "/bulk/SystemConfiguration/"
Print #6, "get systemconfiguration_spatial.MSFICONSPANCONFIG.bulk"
Print #6, "get systemconfiguration_spatial.MSFICONGRPCONFIG.bulk"
Print #6, "bye"
Close #6

Open "C:\TEST1.bat" For Output As #5
Print #5, "cd c:\"
Print #5, "ftp -s:C:\TEST1.txt " & ip & " > C:\log1.txt"
Print #5, "move systemconfiguration_spatial.MSFICONSPANCONFIG.bulk c:\Temp\"
Print #5, "move systemconfiguration_spatial.MSFICONGRPCONFIG.bulk c:\Temp\"
Close #5

retval = Shell("C:\TEST1.bat", 1)

Application.Wait Now + TimeValue("00:00:5")

Kill "c:\log.txt"
Kill "c:\log1.txt"
Kill "c:\TEST.txt"
Kill "c:\TEST.bat"
Kill "c:\TEST1.txt"
Kill "c:\TEST1.bat"
End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, praztitute
Welcome to the Board !!!!!

about your question 1)

do this test
Code:
Sub test()

Workbooks.Add

    With Range("A1:IV100")
    .Value = 123
    .Copy
    End With
    
'Application.DisplayAlerts = False
ActiveWorkbook.Close
'Application.DisplayAlerts = True

End Sub
then enable the 2 quoted lines
but this will still leave you with a great amount of data on the clipboard ...

You can clear the contents of the Windows Clipboard.
run "test"
Code:
Option Explicit

Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
 
Public Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Function
 
Sub test()

Workbooks.Add

    With Range("A1:IV100")
    .Value = 123
    .Copy
    End With
    
'Application.DisplayAlerts = False
Call ClearClipboard
ActiveWorkbook.Close
'Application.DisplayAlerts = True

End Sub
you don't need the DisplayAlerts-lines since the clipboard is really empty

enjoy the (clip)Board :)

kind regards,
Erik
 

praztitute

New Member
Joined
Sep 6, 2006
Messages
4

ADVERTISEMENT

Thanks for the replies. Turning off the alerts worked perfectly. I still have aproblem with disabling buttons while a macro is running, and then enabling the buttons when the macro has completed.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
I'm a little confused why/if that's a problem.

I don't see how the user can access the buttons while the code is running.

Then again I might be missing something.:eek:
 

praztitute

New Member
Joined
Sep 6, 2006
Messages
4
Oooooops. Sorry. While the macro is running I can't click on any of the other buttons. So all is well. Sorry about that brain fart.

Thanks!
 

Forum statistics

Threads
1,136,354
Messages
5,675,294
Members
419,559
Latest member
BraytonM

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
Top