VBA - Access2003 please review my code

First let me send my thanks out to Pieter who has helped me a great deal on 
something else but that help has allowed me to get to this point, and 
Wolfgang who directed me to an MSDN site where I found the rest of what I 
needed for this one.
Thanks to you all..

I have written the code below to run as a toggle from a command button and 
everything seems to work fine except displaying the changes to the properties 
of the commandbutton.  Specifically the transparent property.  The button 
should be transparent when the StartUp Options are set to secure the DB, and 
visible when the DB is unsecured.  I expect that it has something to do with 
the ability or inability to save the form, and If I am correct how can I tell 
it to save the form before closing?

I would appreciate any and all comments regarding the code that I’ve written 
and how it could be done better.

Private Sub ToggleStartUpOptions_Click()
    Dim dbs As Object
    Const PROPERTY_NOT_FOUND As Integer = 3270
    Const ITEM_NOT_IN_COLLECTION As Integer = 3265
    Const TEXT_TYPE As Integer = 10 'Equivalent to DAO dbText data type.
    Const BOOL_TYPE As Integer = 1 'Equivalent to DAO dbBoolean data type.
    Const LONG_TYPE As Integer = 4 'Equivalent to DAO dbLong data type.
    Set dbs = Application.CurrentDb

OpenStartUpProperties:
    On Error GoTo ErrorHandler
    If Me!ToggleStartUpOptions.Caption = "." Then
        Me!ToggleStartUpOptions.Caption = "OFF"
        Me!ToggleStartUpOptions.Transparent = False
        dbs.Properties("AllowFullMenus") = True
    MsgBox "AllowFullMenu setting is  > " & 
dbs.Properties("AllowFullMenus").Value
        dbs.Properties("AllowShortcutMenus") = True
        dbs.Properties("StartupShowDBWindow") = True
        dbs.Properties("AllowSpecialKeys") = True
        dbs.Properties("AllowBuiltInToolBars") = True
        dbs.Properties("AllowToolbarChanges") = True
        dbs.Properties("AllowBypassKey") = True
    Else
        Me!ToggleStartUpOptions.Caption = "."
        Me!ToggleStartUpOptions.Transparent = True
        dbs.Properties("AllowFullMenus") = False
    MsgBox "AllowFullMenu setting is  > " & 
dbs.Properties("AllowFullMenus").Value
        dbs.Properties("AllowShortcutMenus") = False
        dbs.Properties("StartupShowDBWindow") = False
'        dbs.Properties("AllowSpecialKeys") = False
        dbs.Properties("AllowBuiltInToolbars") = False
        dbs.Properties("AllowToolbarChanges") = False
        dbs.Properties("AllowBypassKey") = False
    End If
    
ExitLine:
    On Error Resume Next
    dbs.Close
    Set dbs = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "ERROR#>>  " & Err.Number & " --DESCRIPTION>>  " & Err.Description
    Resume Next
    
End Sub

0
Utf
10/31/2007 7:46:01 PM
access 16762 articles. 3 followers. Follow

6 Replies
727 Views

Similar Articles

[PageSpeed] 36

You could try Me.Repaint at the end of your code to repaint the form...

Steve
-- 
Steve McGuire
MCSD, MCAD, MCP


"Programmer - wannaB" wrote:

> First let me send my thanks out to Pieter who has helped me a great deal on 
> something else but that help has allowed me to get to this point, and 
> Wolfgang who directed me to an MSDN site where I found the rest of what I 
> needed for this one.
> Thanks to you all..
> 
> I have written the code below to run as a toggle from a command button and 
> everything seems to work fine except displaying the changes to the properties 
> of the commandbutton.  Specifically the transparent property.  The button 
> should be transparent when the StartUp Options are set to secure the DB, and 
> visible when the DB is unsecured.  I expect that it has something to do with 
> the ability or inability to save the form, and If I am correct how can I tell 
> it to save the form before closing?
> 
> I would appreciate any and all comments regarding the code that I’ve written 
> and how it could be done better.
> 
> Private Sub ToggleStartUpOptions_Click()
>     Dim dbs As Object
>     Const PROPERTY_NOT_FOUND As Integer = 3270
>     Const ITEM_NOT_IN_COLLECTION As Integer = 3265
>     Const TEXT_TYPE As Integer = 10 'Equivalent to DAO dbText data type.
>     Const BOOL_TYPE As Integer = 1 'Equivalent to DAO dbBoolean data type.
>     Const LONG_TYPE As Integer = 4 'Equivalent to DAO dbLong data type.
>     Set dbs = Application.CurrentDb
> 
> OpenStartUpProperties:
>     On Error GoTo ErrorHandler
>     If Me!ToggleStartUpOptions.Caption = "." Then
>         Me!ToggleStartUpOptions.Caption = "OFF"
>         Me!ToggleStartUpOptions.Transparent = False
>         dbs.Properties("AllowFullMenus") = True
>     MsgBox "AllowFullMenu setting is  > " & 
> dbs.Properties("AllowFullMenus").Value
>         dbs.Properties("AllowShortcutMenus") = True
>         dbs.Properties("StartupShowDBWindow") = True
>         dbs.Properties("AllowSpecialKeys") = True
>         dbs.Properties("AllowBuiltInToolBars") = True
>         dbs.Properties("AllowToolbarChanges") = True
>         dbs.Properties("AllowBypassKey") = True
>     Else
>         Me!ToggleStartUpOptions.Caption = "."
>         Me!ToggleStartUpOptions.Transparent = True
>         dbs.Properties("AllowFullMenus") = False
>     MsgBox "AllowFullMenu setting is  > " & 
> dbs.Properties("AllowFullMenus").Value
>         dbs.Properties("AllowShortcutMenus") = False
>         dbs.Properties("StartupShowDBWindow") = False
> '        dbs.Properties("AllowSpecialKeys") = False
>         dbs.Properties("AllowBuiltInToolbars") = False
>         dbs.Properties("AllowToolbarChanges") = False
>         dbs.Properties("AllowBypassKey") = False
>     End If
>     
> ExitLine:
>     On Error Resume Next
>     dbs.Close
>     Set dbs = Nothing
>     Exit Sub
> 
> ErrorHandler:
>     MsgBox "ERROR#>>  " & Err.Number & " --DESCRIPTION>>  " & Err.Description
>     Resume Next
>     
> End Sub
> 
0
Utf
10/31/2007 8:11:00 PM
Thanks Steve, but that doesn't seem to help.  If I do not have that command 
in there and I click the button over and over the button appears to change, 
but leave the screen and come back and it does not correctly reflect the 
startup conditions.  With the button in place I never see the button change 
and message is always False...  seems like that me.repaint stops the changes. 
 I do appreciate your time, and efforts, thanks.

"SteveM" wrote:

> You could try Me.Repaint at the end of your code to repaint the form...
> 
> Steve
> -- 
> Steve McGuire
> MCSD, MCAD, MCP
> 
> 
> "Programmer - wannaB" wrote:
> 
> > First let me send my thanks out to Pieter who has helped me a great deal on 
> > something else but that help has allowed me to get to this point, and 
> > Wolfgang who directed me to an MSDN site where I found the rest of what I 
> > needed for this one.
> > Thanks to you all..
> > 
> > I have written the code below to run as a toggle from a command button and 
> > everything seems to work fine except displaying the changes to the properties 
> > of the commandbutton.  Specifically the transparent property.  The button 
> > should be transparent when the StartUp Options are set to secure the DB, and 
> > visible when the DB is unsecured.  I expect that it has something to do with 
> > the ability or inability to save the form, and If I am correct how can I tell 
> > it to save the form before closing?
> > 
> > I would appreciate any and all comments regarding the code that I’ve written 
> > and how it could be done better.
> > 
> > Private Sub ToggleStartUpOptions_Click()
> >     Dim dbs As Object
> >     Const PROPERTY_NOT_FOUND As Integer = 3270
> >     Const ITEM_NOT_IN_COLLECTION As Integer = 3265
> >     Const TEXT_TYPE As Integer = 10 'Equivalent to DAO dbText data type.
> >     Const BOOL_TYPE As Integer = 1 'Equivalent to DAO dbBoolean data type.
> >     Const LONG_TYPE As Integer = 4 'Equivalent to DAO dbLong data type.
> >     Set dbs = Application.CurrentDb
> > 
> > OpenStartUpProperties:
> >     On Error GoTo ErrorHandler
> >     If Me!ToggleStartUpOptions.Caption = "." Then
> >         Me!ToggleStartUpOptions.Caption = "OFF"
> >         Me!ToggleStartUpOptions.Transparent = False
> >         dbs.Properties("AllowFullMenus") = True
> >     MsgBox "AllowFullMenu setting is  > " & 
> > dbs.Properties("AllowFullMenus").Value
> >         dbs.Properties("AllowShortcutMenus") = True
> >         dbs.Properties("StartupShowDBWindow") = True
> >         dbs.Properties("AllowSpecialKeys") = True
> >         dbs.Properties("AllowBuiltInToolBars") = True
> >         dbs.Properties("AllowToolbarChanges") = True
> >         dbs.Properties("AllowBypassKey") = True
> >     Else
> >         Me!ToggleStartUpOptions.Caption = "."
> >         Me!ToggleStartUpOptions.Transparent = True
> >         dbs.Properties("AllowFullMenus") = False
> >     MsgBox "AllowFullMenu setting is  > " & 
> > dbs.Properties("AllowFullMenus").Value
> >         dbs.Properties("AllowShortcutMenus") = False
> >         dbs.Properties("StartupShowDBWindow") = False
> > '        dbs.Properties("AllowSpecialKeys") = False
> >         dbs.Properties("AllowBuiltInToolbars") = False
> >         dbs.Properties("AllowToolbarChanges") = False
> >         dbs.Properties("AllowBypassKey") = False
> >     End If
> >     
> > ExitLine:
> >     On Error Resume Next
> >     dbs.Close
> >     Set dbs = Nothing
> >     Exit Sub
> > 
> > ErrorHandler:
> >     MsgBox "ERROR#>>  " & Err.Number & " --DESCRIPTION>>  " & Err.Description
> >     Resume Next
> >     
> > End Sub
> > 
0
Utf
10/31/2007 8:34:02 PM
Hi.

> Specifically the transparent property.  The button
> should be transparent when the StartUp Options are set to secure the DB, 
> and
> visible when the DB is unsecured.

Your code makes the button invisible and toggles it back to visible again 
when Microsoft Office 2003 SP-1 is installed.  I take it your button isn't 
turning invisible on your form when you try it?  Perhaps you've installed 
the new Microsoft Office SP-3?

> I would appreciate any and all comments regarding the code that I've 
> written
> and how it could be done better.

Okay.  I'll mostly ask for your reasoning on why you wrote the code you did. 
You need to have good reasons for using non-standard programming practices, 
instead of "I copied it from code I found on my Easter Egg hunts on the 
Internet, so I have no idea what it's for."

>    Dim dbs As Object

Why are you using a generic object instead of the DAO database object?  All 
versions of Access can find the DAO library without stumbling on missing 
references, even if it's installed in a non-standard directory.

>    Const PROPERTY_NOT_FOUND As Integer = 3270
>    Const ITEM_NOT_IN_COLLECTION As Integer = 3265
>    Const TEXT_TYPE As Integer = 10 'Equivalent to DAO dbText data type.
>    Const BOOL_TYPE As Integer = 1 'Equivalent to DAO dbBoolean data type.
>    Const LONG_TYPE As Integer = 4 'Equivalent to DAO dbLong data type.

Why are you declaring all of these local constants -- and then never using 
them within the procedure?

> OpenStartUpProperties:

Why are you placing this label in the code, and then never referencing it 
when redirecting logic flow?  (As a matter of fact, there is no redirecting 
of logic flow in your procedure except for the On Error commands, so the 
extra label is unnecessary.)

>    On Error Resume Next

If you didn't put a stumbling block in your code cleanup section, you 
wouldn't need to tell Access to ignore it.  And the use of "Resume Next" 
almost always means the programmer is too lazy -- or lacks the skills -- to 
fix his buggy code.  Handle the errors your procedure encounters by fixing 
the code instead of ignoring them, and you'll become a better programmer.

>    dbs.Close

Why are you closing an object you didn't open programmatically in your code?

> ExitLine:

Again, why are you placing this label in the code, and then never 
referencing it when redirecting logic flow?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact 
info.


0
69
10/31/2007 10:06:01 PM
Hi Gunny.

Let’s start at the top;  The code makes the button transparent, not invisible
I believe that if it were invisible, it would not be usable.  And SP-1 has
nothing to do with any of this.  The caption, and transparency properties 
of the button are changed with each click of the button in order to signify 
the conditions that the StartUp Options have been set to, and to effect the
toggle ability.

The reasoning for this code is to be able to toggle the StartUp Options, 
including the ability to prevent the use of the shift key when the DB is
opened.  Without this code how could the DB be locked down and still
have the ability for developers to make changes.  The button dimensions are
slightly bigger then a . and is Transparent while the StartUp Options are set
to restrict structural changes.  One needs to know where the hidden button
is in order to modify the structure of the database.
>>>>>> What is it about this that’s non-standard programming practices ????

I do not know enough to fully understand the difference between a generic 
object
and a DAO object, and this is a reference that I found that worked.
>>>> WHY would MSDN provide non-standard programming code

These constants were used in the first run through, and were needed in the
Error checking that was used to initially create the AllowByPassKey Property.

I created that label when I first started and later cleaned it up to use
the If Then Else...  just failed to notice that it was no longer used

Granted I may be lazy, but in this case I would have to say the reason is that
I lack the Skills, and that is one reason why I post my creations and ask for
input, such as yours.

I thought that dbs.close was like closing the window that is used when you set
the options in the StartUp Options window.

Again another of those left over from trial and error programming until I got 
what I was looking for.

Yes I did go on an Easter egg hunt in search of some code that would get me 
started on the project at hand.  Using bits and pieces from the site listed 
below I came up with the code that seemed to do what I was looking for.
http://msdn2.microsoft.com/en-us/library/aa662934(office.11).aspx

Public Sub SetMDBAppTitle()
   Dim dbs As Object
   Dim prp As Object
   Dim strTitle As String

   Const PROPERTY_NOT_FOUND As Integer = 3270
   Const TEXT_TYPE As Integer = 10
   ' Equivalent to DAO dbText data type.
   Const BOOL_TYPE As Integer = 1
   ' Equivalent to DAO dbBoolean data type.
   Const LONG_TYPE As Integer = 4
   ' Equivalent to DAO dbLong data type.

   On Error GoTo SetMDBAppTitle_Err

   Set dbs = Application.CurrentDb
   strTitle = "Setting *.MDB Startup Options"

   ' Try to set the property. If it fails, the property does not exist.
   dbs.Properties("AppTitle") = strTitle

   ' Refresh the title bar to reflect the change.
   Application.RefreshTitleBar

ExitLine:
   dbs.Close
   Set dbs = Nothing
   Set prp = Nothing
   Exit Sub

SetMDBAppTitle_Err:
   If Err.Number = PROPERTY_NOT_FOUND Then
      ' Create the new property.
      Set prp = dbs.CreateProperty("AppTitle", TEXT_TYPE, strTitle)
      dbs.Properties.Append prp
      Resume Next
   Else
      Resume ExitLine
   End If

End Sub

Public Sub RemoveMDBAppTitle()
   Dim dbs As Object

   Const ITEM_NOT_IN_COLLECTION As Integer = 3265

On Error GoTo RemoveMDBAppTitle_Err

   Set dbs = Application.CurrentDb

   ' Remove the startup property.
   dbs.Properties.Delete ("AppTitle")

   'Refresh the title bar to reflect the change.
   Application.RefreshTitleBar

ExitLine:
   dbs.Close
   Set dbs = Nothing
   Exit Sub

RemoveMDBAppTitle_Err:
   If Err.Number = ITEM_NOT_IN_COLLECTION Then
      Resume ExitLine
   Else
      Resume Next
   End If

End Sub

Thanks,
69TA
=========================================
"'69 Camaro" wrote:

> Hi.
> 
> > Specifically the transparent property.  The button
> > should be transparent when the StartUp Options are set to secure the DB, 
> > and
> > visible when the DB is unsecured.
> 
> Your code makes the button invisible and toggles it back to visible again 
> when Microsoft Office 2003 SP-1 is installed.  I take it your button isn't 
> turning invisible on your form when you try it?  Perhaps you've installed 
> the new Microsoft Office SP-3?
> 
> > I would appreciate any and all comments regarding the code that I've 
> > written
> > and how it could be done better.
> 
> Okay.  I'll mostly ask for your reasoning on why you wrote the code you did. 
> You need to have good reasons for using non-standard programming practices, 
> instead of "I copied it from code I found on my Easter Egg hunts on the 
> Internet, so I have no idea what it's for."
> 
> >    Dim dbs As Object
> 
> Why are you using a generic object instead of the DAO database object?  All 
> versions of Access can find the DAO library without stumbling on missing 
> references, even if it's installed in a non-standard directory.
> 
> >    Const PROPERTY_NOT_FOUND As Integer = 3270
> >    Const ITEM_NOT_IN_COLLECTION As Integer = 3265
> >    Const TEXT_TYPE As Integer = 10 'Equivalent to DAO dbText data type.
> >    Const BOOL_TYPE As Integer = 1 'Equivalent to DAO dbBoolean data type.
> >    Const LONG_TYPE As Integer = 4 'Equivalent to DAO dbLong data type.
> 
> Why are you declaring all of these local constants -- and then never using 
> them within the procedure?
> 
> > OpenStartUpProperties:
> 
> Why are you placing this label in the code, and then never referencing it 
> when redirecting logic flow?  (As a matter of fact, there is no redirecting 
> of logic flow in your procedure except for the On Error commands, so the 
> extra label is unnecessary.)
> 
> >    On Error Resume Next
> 
> If you didn't put a stumbling block in your code cleanup section, you 
> wouldn't need to tell Access to ignore it.  And the use of "Resume Next" 
> almost always means the programmer is too lazy -- or lacks the skills -- to 
> fix his buggy code.  Handle the errors your procedure encounters by fixing 
> the code instead of ignoring them, and you'll become a better programmer.
> 
> >    dbs.Close
> 
> Why are you closing an object you didn't open programmatically in your code?
> 
> > ExitLine:
> 
> Again, why are you placing this label in the code, and then never 
> referencing it when redirecting logic flow?
> 
> HTH.
> Gunny
> 
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact 
> info.
> 
> 
> 
0
Utf
11/1/2007 3:21:01 PM
my  windows media player 11 is not showing pictures , i only hear the audio 
but no video output.pls who can help me 

0
opeyemi
11/1/2007 4:26:32 PM
Sorry, this newsgroup is for questions about Access, the database product 
that's part of Office Professional.

You'll need to repost your question to a more appropriate newsgroup, such as 
microsoft.public.windowsmedia.player

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"opeyemi" <mcdenitz@microsoft.com> wrote in message 
news:D9F65772-7631-41DD-817D-75F29089B357@microsoft.com...
> my  windows media player 11 is not showing pictures , i only hear the 
> audio but no video output.pls who can help me 


0
Douglas
11/1/2007 5:15:21 PM
Reply:

Similar Artilces:

which code for the SpellLang in English ?
Hi, I created an Excel file (XP/XL:2000) which is using the check spelling with the code : Range(Cells(19, 2), Cells(50, 9)).CheckSpelling SpellLang:=1033 I would like to know the valid number version for the SpellLang, according to the US language The number 1033 is actually valid for the french language of north America .... Fran�ois I just ran a test using English and the Macro recording feature. It spit this out: ActiveCell.FormulaR1C1 = "spelling something wronge" Range("J6").Select Cells.CheckSpelling SpellLang:=1033 It would appear that 103...

Somebody please help me!
When I go to sign on to Microsoft Outlook 2000, it decided today to ask me for a password. I can't remember my password, and can't figure out how to find it. I can't exactly have them email my password if I can't get into outlook, and I don't know who to contact anyway. Does anybody know where I can go, or what telephone number to call? is this a password to connect to your ISP, or a password to open your pst file? "Jessi Baumgartner" <anonymous@discussions.microsoft.com> wrote in message news:043801c3a3d9$8d812e30$a301280a@phx.gbl... > When I ...

How to code it in macro? 05-18-10
There is a list of files under column A, I would like to create a macro to open - updating all links - save - close each file one by one for each file from A2 to A20. There is one condition to process each file, for example; The following files will not be processed until the last updated for this file C:\documents\A.xls is today, then open - updating all links - save - close following files, C:\documents\A1.xls in cell A2 C:\documents\A2.xls in cell A3 C:\documents\A3.xls in cell A4 The following files will not be processed until the last updated for this file C:\docum...

please help
Dear All I Hope you can help me please, i have made up my price list for my business on excel . sheets 1 to 3 are materials and they are all added up together on sheet 4.(sheet 4 giving me my final materials cost) now sheet 5 is my costs plus profit. this is all fine now my problem is. i would like to cut and paste sheet 5 on to an other folder so i can email it to clients. But when i do this most of the values disappear as i have broken the formlas to give those values, so can some please help me and tell me how i can, cut and paste so i have just my price list with all the values on it...

Outlook error codes
I would appreciate any help! Outlook 2007 refuses to startup, error messsage: catastrophic failure (0x8000FFFF) and the following: C:\Users\Matt\AppData\Local\Microsoft\Outlook\ Outlook.pst is not a personal folders file. error code now being displayed: 0x80040119 (is this a registry problem?) the above was the default folder and as such contains a large amount of my inaccessible data Have been unable to activate (or even locate) inbox repair tool (Scanpst.exe), which I understand was installed with Outlook have neutralized spyware uninstalled and reinstalled Outlook 2007, to no ...

escape from vba-loop
hi group, I've got an excel-sheet containing some vba-code. There's one sub that does some calculations in a loop which can run for some time. My users now want to be able to escape from this calculation by pressing <escape> or clicking a button on the sheet. But actually excel is frozen while calculating. What is the most common way to achieve this? I guess it must be possible to solve this with excel-events, but I'm not very experienced in using them. thanks for any hints, stephan You can add a "DoEvents" statement inside the loop. Excel checks for any instruc...

VBA question confused
I have a form set up with a recordset. I'm also using a filter. Me.Filter = "position IN ('custody','sgt','lieutenant','captain', 'other') and shift IN ('a-days','day shift','afternoon shift')" Me.FilterOn = True I'm trying to get an order by the last name field. I read Allen Brown's page about setting the order. I've also read other pages on this board that basically says it can't be done. So, I wrote an SQL that would give me what I want. strSelect = "SELECT * " & _ ...

bar code help, adding asterix to reference
Hi, A simple one im sure, cell b4 contains a number, i want cell c4 to display the same number with an asterix (asterisk?) at either side for my bar code font? ="*",B4,"*" What's the correct way to enter this? Many thanks as ever, Rick ="*"&A1&"*" seems to work after some trial and error, could there arise a problem doing it this way? Thanks, Rick "R D S" <ricknewsgroup@members.v21.co.uk> wrote in message news:352cf1F4gusjfU1@individual.net... > Hi, > A simple one im sure, cell b4 contains a number, i want cell c4...

VBA help #3
Hi, I'm an excel VBA total novice. I dabble in access vba, but am unsure where to start in excel. What I need is some code I can tie to a macro (& keyboard shortcut) which will find all values under 1000 in the selected range and change them to 1001. Can anyone point me in the right direction? Any help greatly appreciated.....thanks, Jason Look in the vba help index for FINDNEXT. There is a good example. c.value=1001 -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Jay" <dummy@dummy.dummy> wrote in message news:eYGxqeELIHA.3...

Select an array of worksheets VBA
I am trying to write a macro that will select an array of worksheets. The number of worksheets may vary from book to book so I tried usin the following: Sheets(Array("Sheet (1)", Sheets(Sheets.Count))).Select but this doesn't work. Can anyone help me out please:confused -- Jamuc ----------------------------------------------------------------------- Jamuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3054 View this thread: http://www.excelforum.com/showthread.php?threadid=52431 Are you trying to select all the sheets? sheets.select maybe??...

Adding to a list of codes
Hi In Excel 2003 I have a spreadsheet listing a large number of cost centres. Each month I am given a list of the cost centres and expenditure against them, which I want to use a look up to populate in to a master spreadsheet. However, during the month new cost centres could have been created. What I would like to be able to do is run a macro against the monthly return and compare the codes contained in it to the codes already listed in the master sheet. If there are any new codes not already in the master sheet I would want them added to the existing list so that the mast...

How to select combobx value with vba?
For an electrical part number quoting application developed in Excel 2000, I have a a userform (userform4) with a combobox (cboQpn) to hold the quoted part number, and another combobox (cboFormula) prepopulated with a named range (Partnum) The user types in a part number in cboQpn and then chooses the correct formula from cboFormula. Now in access I have a table (tblDetail) that holds previously quoted part numbers and their details--the formula used in the previous quote, for example. The formula information is in column 5 in tblDetail I want to emulate the manual selecting o...

VBA Function to explode string
Hi. I am wanting to know hbow I would go about getting the following 3 cells: Cells A1, A2, A3: :: 1-4,6,8 50 S1B1:: Into a form that looks more like this ::A1 A2 A3 1 8.33 S1B1 2 8.33 S1B1 3 8.33 S1B1 4 8.33 S1B1 6 8.33 S1B1 8 8.35 S1B1:: How it does the rounding (and on which one), it does not matter! Thanks, Tom -- tomjermy ------------------------------------------------------------------------ tomjermy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24666 View this thread: http://www.excelforum.com/showthread.php?threadid=382414 You haven't exp...

Please Help?!?
I recently had to format my hard drive and saved my "Identities" folder which holds all of my old Outlook Express information. I am wondering how to import my address book/old emal/etc out of that Identities folder to Outlook itself. I don't want to lose all of those emails. Please help me! How do I get all of my old settings and folders into Outlook? This group is for Outlook Support. Try an Outlook Express support option. you can find options here: http://support.microsoft.com/default.aspx?scid=fh;%5Bln%5D;oex&product=msall "CyAndLako" <CyAndLako...

C-DLL-VBA-EXCEL strings
Hello, I'm trying to connect excel to a C dll library (call C dll from excel through the VBA). It works well for returning integer and double values (see simple example below), but I can't seem to get it to return strings. I am using MinGW gcc (so basically only C) to construct my dll, so I don't have access to BSTR and other cpp like objects/functions... Is there a way to make the C dll return strings to excel? Thanks DLL.c #ifdef BUILD_DLL #define EXPORT __declspec(dllexport) #else #define EXPORT __declspec(dllimport) #endif EXPORT int __stdcall add2(int num){ return num + 2; ...

Getting a Return Value from a Stored Procedure in VBA
Hi all, I have a Stored procedure, here: PROCEDURE [dbo].[new_tbox] -- Add the parameters for the stored procedure here @slide_id int, @shape_id int, @cnt_typ_id int AS BEGIN DECLARE @cnt_id int, @tbox_id int INSERT cnt_info VALUES(@slide_id, @shape_id, @cnt_typ_id) SET @cnt_id = @@IDENTITY INSERT tbox_cnt VALUES(@cnt_id) SET @tbox_id = @@IDENTITY RETURN @tbox_id END That returns the variable. This works in SQL Server, and I get the return value My VBA code from my ADP is as such: With cmd .ActiveConnection = ConnStr .CommandText = "...

VBA calling sp with ANSI_NULLS set ON
Hi, On Query Analyzer, I created a sp as follow: > > if exists (select * from dbo.sysobjects where id = object_id(N'dbo.PCS_SEG1') and OBJECTPROPERTY(id, N'IsProcedure')= 1) DROP PROCEDURE dbo.PCS_SEG1 GO CREATE PROCEDURE dbo.PCS_SEG1 as set ANSI_NULLS on set ANSI_WARNINGS on ** sql statement *** > > I need the to set ANSI_NULLS and ANSI_WARNINGS on as it involves another server using linked server. When executing the sp from query analyzer, it works fine too: > exec PCS_SEG1 > But, when I called from VBA, I received error: Heterogeneous queries require t...

Splitting code to modules ... HOW ?
I have build several Excel VBA applictions, but many of them use som same code and functions. I like to put the code into ONE base module. How must I create the module, export it ? And how to refer to it in al the programs I like to use the base module ?? Roel -- Message posted from http://www.ExcelForum.com Roel If the macros are in several workbooks copy them into the same book and save this as Personal.xls The personal workbook may not be visible when you start Excel but the macros will be available to all workbooks. Alteratively, save the macro as an addin e.g. "myFile.xla&...

Hiding and Unhiding Forms In Code
This is what I am trying to do. I hide a form and opened another form using a button: Me.Visible=False DoCmd.Open "Select Report Type", acNormal Now I want to unhide the form again using a button on the second form, I tried: If Forms![Inscope Site Summary].IsVisible=False Then Forms![Inscope Site Summary].Visible=true End If But this didn't work. I am stuck. Any help will be greatly appreciated. Thank you. Here is an example of using the IsVisible property (straight from your friendly Access help file); If Me!CategoryID.IsVisible Then Me!Line0.Visible = Tr...

how do i change or make this macro??? pictures and code included
i need to either make this from scratch or change it to suit my needs. Firstly a link to the template it is from http://www.exinfm.com/excel%20files/FY2000WhatIf.xls the code is Sub projection() Dim revgrowth As Single, costpercent As Single, smgrowth As Single devgrowth As Single, gagrowth As Single Dim intincome As Integer, noncontitems As Integer, otherexps A Integer Dim taxrate As Single, avgshares As Integer 'dialog box gets displayed With DialogSheets("dialog3") If .Show = False Then Exit Sub End If revgrowth = .EditBoxes(1).Text costpercent = .EditBoxes(2).Text smgrow...

Colour code from different servers
-- I collect mail from 2 different servers and I was wondering if it is possible to have them show in the IN BOX in different colours. I know I can select different senders by colour but I want to show all senders that came from each server. Can anybody help Pete A "Pete A" <PeteA@discussions.microsoft.com> wrote in message news:EB77DCA6-A07E-47E9-AD04-62C8B6BC1193@microsoft.com... > > -- I collect mail from 2 different servers and I was wondering if it is > possible to have them show in the IN BOX in different colours. I know I > can > select different se...

I need help please...
In fact I need an "excel brain" to help me for some calculations. I have in excel : DATE COUNTRY RATE1 RATE2 COL(A) COL(B) COL(C) COL(D) 3 GER 1.50 2.30 3 FRA 1.15 6.50 4 GER 1.20 6.00 4 GER2 1.40 5.50 4 IT1 1.80 3.60 6 IT2 3.50 2.11 7 POL 4.10 ...

Downloading emails won't happen
My emails down load for the first four only, and the fifth email goes really really slow and won't download and then drops out at the end, there are emails behind this also. I am not computer minded and are up the creek with out a paddle and sinking fast so to speak, please help me i have not had this problem before. Close WinMail an go to your server's Webmail site. Log in and delete the fifth message. It is probably way to large to handle. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "windermere" <windermere@discussions....

I fixed the date and time and the time zone. Now reply me please
Hi, I have some questions in outlook 2003, can anyone help me? 1-how can i in the appointment type a subject with special format (red, bold..) 2-whent a task is 100% completed, how can be deleted automatically. 3- I need to filter only the available time on a specific day. ex if i have 2 appointments today on a specific time, what i need to see is the available time for other new appointments. 4-If I have a lot of appointments on a specific date that are colored with labels and have a specific show time as (busy,tentative), when i go to print this calendar with the "calendar detai...

What's the deal with List View ignoring LVIF_STATE in OnLvnGetdispinfo Microsoft, please comment !
Hello all. I have whis wierd problem with list view ignoring LVIF_STATE in OnLvnGetdispinfo message handler. It is a custom drawn virtual list. When the list is initially displayed, I'm not getting any LVIF_STATE requests.for the items displayed, only when I scroll the list I start getting it, but list ignores it flat-out. Also, how come I'm not getting any OnLvnSetdispinfo messages? I've run into so many incosistencies when dealig with the virtual custom drawn list that's quickly becoming the nightmare. Here's the current bug list: 1. Keyboard selection bug: when ...