PDA

View Full Version : Solved: how to prevent macro name visible to user.



VISHAL120
03-22-2012, 12:13 AM
Hi i just needed some help on how can i hide the macro name or procedure from user.

Normally i have place a button to click so that user can click on the button to run the macro. but the name of the macro is available on the Visual Basic toolbar where somtimes instead of clicking the buttons they are just running it from the toolbar which sometimes on the main file causes error afterwards.

I have try using application run but has not been able to do it.

Can you please just explain how i can hide the sub names from user.

here is my code actually:
Sub Formulating_data_GROUP()
starttime = Timer

Dim LR As Long
Application.ScreenUpdating = False

With ThisWorkbook.Sheets("Shadow_k_mins")
LR = .Range("B" & .Rows.Count).End(xlUp).Row
End With
Application.Calculation = xlCalculationManual

'COPY COUNTIF FORMULA
Range("CJ35").Select
Selection.Copy
Range("$AD$35:AD" & LR).Select
Selection.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

'COPY THE MAIN LOADING FORMULA.

Range("$CK$35:EL35").Select
Selection.Copy
Range("$AG$35:CH" & LR).Select
Selection.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
With Range("$AD$36:CH" & LR)
Application.Calculation = xlCalculationAutomatic
.Value = .Value
End With


MsgBox Timer - starttime & " secs."
End Sub


Ana also a sample workbook as info. sorry am unale to attach file for the moment may some problem on the database as it mentioning here.

thanks in advance for the help.

Bob Phillips
03-22-2012, 12:58 AM
Change if from a sub to a function, OR

add Option Private Module to the code module at te top.

Bob Phillips
03-22-2012, 01:00 AM
Also, no selects please



Sub Formulating_data_GROUP()
starttime = Timer

Dim LR As Long
Application.ScreenUpdating = False

With ThisWorkbook.Sheets("Shadow_k_mins")
LR = .Range("B" & .Rows.Count).End(xlUp).Row
End With
Application.Calculation = xlCalculationManual

'COPY COUNTIF FORMULA
Range("CJ35").Copy
Range("$AD$35:AD" & LR).PasteSpecial Paste:=xlPasteAll

'COPY THE MAIN LOADING FORMULA.

Range("$CK$35:EL35").Copy
Range("$AG$35:CH" & LR).PasteSpecial Paste:=xlPasteAll
With Range("$AD$36:CH" & LR)
Application.Calculation = xlCalculationAutomatic
.Value = .Value
End With

MsgBox Timer - starttime & " secs."
End Sub

VISHAL120
03-22-2012, 03:28 AM
Hi Bob,

many thanks for your help. it solved.