Excell Macro Help

[DocCon]

Hi
I have a basic knowledge of excel. I have a form that has a name setup button on the tool bar that starts a edit box Macro. What I would like to know if it is possable to return a value in the edit box from the active workbook if the value = true. Below is sample. Thanks for any help.

' Top of Module

' dialogname Macro
' Opens the NameDlog Dialog sheet for user input of customer name, code,
' phone, address, and statement date.
'
'CHANGE HISTORY:
' <date> <author> <description>.
Sub dialogname()

Application.ScreenUpdating = False
' Windows(&quot;TESTCODE.XLS&quot.Activate
' ActiveWindow.Visible = False
Workbooks(&quot;TEST.XLS&quot.DialogSheets(&quot;NameDlog&quot.Show

End Sub

'
' nameok Macro
' On click of OK button, the user inputed information is transferred
' from the appropriate edit boxes to the appropriate cell references
' on the appropriate sheets.
'
'CHANGE HISTORY:
' <date> <author> <description>.
Sub nameok()

'Hide updates from user
Application.Cursor = xlWait
Application.ScreenUpdating = False

Application.ScreenUpdating = False
' Edit Box5_Change Macro
CustName = Workbooks(&quot;TESTCODE.XLS&quot.DialogSheets(&quot;NameDlog&quot.EditBoxes(1).Text
Application.Sheets(&quot;FIN STMT&quot.Activate
ActiveSheet.Unprotect
Range(&quot;A2&quot.Value = CustName

 

[rdodge]

In Excel, I have used UserForms, which is created in VBA. There's various VBA general coding things you can learn based on what I see here.

Declaration of all variables/objects are recommended to take out any sort of guess work and makes it easier for debugging purpose.

Example:

Dim wshCUS as Worksheet, NameD as ufmUserform

When an object is being refered to more than once, set an object type variable to it, so as to allow the processing of the code to be more efficient.

Example:

Set wshCUS = Thisworkbook.Worksheets(&quot;Customer&quot
Set ufmNameD = Userforms(&quot;NameDlog&quot
wshCUS.Range(&quot;A2&quot.Value = ufmNameD.CustName.Text

Use a standard naming convention for the type of object/variable it is. This helps in the debugging phase and can also help prevent from the code having to choose between 2 variables/objects with the same name otherwise, which can create a lot of unwanted issues.

Avoid using Activate/Select methods whenever possible as the example above demostrates a direct way of assigning variables without necessarily having to move the focus of the application.

Prequalify your objects/variables so as to help keep ambiguity to a minimal. Note, object type variables DOES NOT get prequalified with it's parent object cause it's already been established via the Set Statement (wshCUS worksheet variable for instance). However, public variables should be prequalified with it's module name and maybe even with it's project name.

I.e.

Dim strFstLtr as String
strFstLtr = VBA.Strings.Left(&quot;Test&quot;,1)

Keep variable names as short as possible for more efficient processing (Some may argue this is no longer needed cause systems runs a lot faster than they use to), but at the same time, use meaningful variable names to make debugging easier.

These are just some things to keep in mind while working with code in VBA.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.