Can Someone Adjust These VB Codes PLEASE...

[Mister H]

Hi:

I am new to VB and quite unfamiliar with the different commands. I am using the following code and was wondering if it could be adjusted.

Sub RestoreDepositForm()
                  
' (FINAL)

Answer = MsgBox("WARNING!  Do you really want to Restore The Deposit Form?" & vbCr & vbCr & "Doing so will DELETE the current Deposit Form and ALL its data.", vbOKCancel, "WARNING!")

If Answer = vbCancel Then Exit Sub

If Answer = vbOK Then

If Answer2 = vbCancel Then Exit Sub
End If


End Sub

What I want it to do is if the person clicks OK 9to delete) thean I want it to take them to cell " C3 " on the " Control Log " sheet

The other problem I am having with a code that was created (via macro) is that when I save my workbook with a diffeerent name it no longer functions.

Sub PrepareBankSummarySheet()
'
' PrepareBankSummarySheet Macro
' Macro recorded 29/11/2005 (FINAL)
'

'
Range("F30").Select
Application.Run "'Nov 29th FINAL.xls'!ShowAllBankSummaryData"
Range("A2:C401").Select
Selection.FillDown
ActiveWindow.ScrollRow = 1
Range("A2").Select
Application.Run "'Nov 29th FINAL.xls'!HideBankSummaryData"
End Sub

Can this be tweaked to remain functional regardless of the Workbook Name? I know I probably need to replace the Nov 29th FINAL with something else I am just not sure what.

As ALWAYS, THANKS to anyone that can assist.

Bye 4 Now,
Mark
Sudbury, Ontario

 

[Andrew Poulsom]

For your first question:

Sub RestoreDepositForm() 
                  
' (FINAL) 

Answer = MsgBox("WARNING!  Do you really want to Restore The Deposit Form?" & vbCr & vbCr & "Doing so will DELETE the current Deposit Form and ALL its data.", vbOKCancel, "WARNING!") 

If Answer = vbCancel Then Exit Sub 

If Answer = vbOK Then 
   Application.Goto Worksheets("Control Log").Range("C3") 
End If 

End Sub

For your second question where is the Sub PrepareBankSummarySheet and what does it do?

 

[BrianB]

If you want the workbook with the macro in it use ThisWorkbook, otherwise ActiveWorkBook. eg.

'cell " C3 " on the " Control Log " sheet
Application.Goto 
    reference:=ThisWorkbook.Worksheets("Control log").Range("C3"), _
    Scroll:=True

 

[Mister H]

Hi Andrew:

THANKS... In regards to your question the I am not sure what you mean. The Macro Button is located on the "Bank Summary" sheet and what it is supose to do:

Show All Rows (unfilter)
Copy Formulas down from Row 2 to 400
Hide Blank Rows (filter)

As mentioned this works fine until I renmae the book and close the original book. Once I open the newly named workbook the macro no longer works. Hoep this helps. Sorry for the confusion.

Take Care,
Mark

 

[Andrew Poulsom]

Is there only one workbook?

 

[Mister H]

Hi Andrew:

Yes it is one workbook. The workbook contains 7 seperate sheets one of which is "Control Log". I want to be able to send this workbook to multiple users who in turn will rename the book whenever they use it which is why I need the macro (code) to work regardless of the name of the workbook. Hope this clarifies things (a little?).

Bye 4 now,
Mark.

 

[Andrew Poulsom]

So all you need is:

Call ShowAllBankSummaryData

 

[Mister H]

Sub PrepareBankSummarySheet() 
' 
' PrepareBankSummarySheet Macro 
' Macro recorded 29/11/2005 (FINAL) 
' 

' 
Range("F30").Select 
Application.Run "'Nov 29th FINAL.xls'!ShowAllBankSummaryData" 
Range("A2:C401").Select 
Selection.FillDown 
ActiveWindow.ScrollRow = 1 
Range("A2").Select 
Application.Run "'Nov 29th FINAL.xls'!HideBankSummaryData" 
End Sub 


[quote]So all you need is: 

Call ShowAllBankSummaryData

Sorry for the confusion but do I just delete the wording:

"'Nov 29th FINAL.xls'!HideBankSummarData" and replace it with

"HideBankSummaryData"

THANKS for your help.[/quote]

 

[Mister H]

Hi Andrew:

THANKS... I should have experimented BEFORE I sent you the last post. I have now fixed the problem THANKS to your solution.

Have a GREAT day,
Mark