VBA for excel help...

[anthony20069]

hi guys,

wondering if someone would be able to help me out here.

Basically i have created a excel template... when this template is first opened it ask you what the name of the company. When you have saved this spreadsheet, it should not ask you what the name of the company is any more.

I have the following code in ThisWorkbook

Private Sub Workbook_Open()
Dim strName As String
Dim compname
compname = "!?$!$!"
If compname <> strName Then

strName = InputBox(Prompt:="Company Name", _
Title:="ENTER COMPANY NAME")

compname = strName

If strName = "" Or strName = vbNullString Then
MsgBox ("You have not entered a company name.")
Range("Name").Characters.Insert ("No Name Given")
Exit Sub
Else
Range("Name").Characters.Insert (strName)
End If

End If

End Sub

Is there a way to store variables in VBA???

Regards

Anthony

[Mavyak]

You could store the value as a custom property of the worksheet. Post back if you need help coding it.

[anthony20069]

pff straigth over my head :)... help coding would be much appricated

Cheers

[Mavyak]

I can only casually surf/help while I'm at work. I can do this for you tonight/tomorrow if someone else doesn't hook you up beforehand.

[anthony20069]

that would be excellent, really appreciate your help....

cheers

[CreganTur]

While not as good as setting a custom property, you can 'cheat' by writing something into a cell during an intial setup that will be hidden from the user, or in an area of the worksheet the User won't work.

For example, I created a gradebook for a buddy of mine who's a teacher. The first time you use the workbook, there's a setup userform. When the user sets all of their options, the word 'setup' is placed in a cell on Sheet2 (where the other options are stored). So I use this as a part of the Workbook Open event to determine whether or not to show the Form:
If Sheet2.Range("A1").Value <> "setup" Then
GradebookSetup.Show
End If

Sheet2 is protected so the user can't remove the word 'setup'.

[Mavyak]

This goes in the ThisWorkbook code module:

Private Sub Workbook_Open()
On Error GoTo errHandler
Dim p As CustomProperty
Dim w As Worksheet
Dim Company_Name As String
Dim yn As Integer

Set w = Sheet1 'Change this to the applicable sheet

For Each p In w.CustomProperties
If p.Name = "CompanyName" Then
Company_Name = p.Value
Exit For
End If
Next p

If Company_Name <> "" Then
GoTo ExitSub
End If

GetCompanyName:
Company_Name = InputBox("Enter the company name:", "Need some info here...", "Default Company Name")
If Len(Trim(Company_Name)) = 0 Then GoTo GetCompanyName

yn = MsgBox("You entered:" & vbCr & vbCr & Company_Name & vbCr & vbCr & "Is this correct?", vbYesNoCancel, "Confirm Company Name")

If yn = vbNo Then
GoTo GetCompanyName
ElseIf yn = vbCancel Then
GoTo GetOut
End If

w.CustomProperties.Add "CompanyName", Company_Name

ExitSub:
Set w = Nothing
Set p = Nothing
Exit Sub

errHandler:
MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number
GoTo ExitSub

GetOut:
ThisWorkbook.Close SaveChanges:=False
End Sub

Sub Reset_Custom_Property_For_Test()
Dim w As Worksheet

Set w = Sheet1

If w.CustomProperties.Count > 0 Then
Dim p As CustomProperty

For Each p In w.CustomProperties
p.Delete
Next p
End If

Set p = Nothing
Set w = Nothing
End Sub

[anthony20069]

:), that is great... cheers for all your help guys.

**********************

I copied and pasted into the ThisWorkbook code module, changed the Sheet Name and saved it. When I open up the workbook, i recieve the following error message:

Error #424
Object required


Any thoughs, or am I doing something wrong?

******************
Also just realised that this is in the wrong place... sorry, could a admin or someone else move it?
******************

Its ok figured it out, was putting the wrong sheet number... duhhhhhh....

Two more questions :)

First:
Once i have saved this work sheet under a different name, when i try to open up the "Template" one, it doesnt ask for the "Company name" any more

Second:
When you "View Code" under Scroll area, when i enter A1:U55, it only scrolls that range which is correct, but when i save the workbook and open it up again, i can scroll where ever, and the Scroll Range is rest to nothing