View Full Version : VBA for excel help...
anthony20069
09-05-2008, 07:39 AM
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
09-05-2008, 08:16 AM
You could store the value as a custom property of the worksheet. Post back if you need help coding it.
anthony20069
09-05-2008, 08:21 AM
pff straigth over my head :)... help coding would be much appricated
Cheers
Mavyak
09-05-2008, 08:23 AM
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
09-05-2008, 08:50 AM
that would be excellent, really appreciate your help....
cheers
CreganTur
09-05-2008, 09:09 AM
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
09-06-2008, 11:54 AM
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
09-08-2008, 01:59 AM
:), 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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.