Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 18th, 2005, 06:04 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to handle control properties in a form

I like to place buttons on top of each other and then use the Visible and Enabled properties to show/hide/disable them when clicking a specific button. However, as it's a pain to code in each button event, i've tried to use a procedure as shown below.

But it's unstable. Clicking first Commandbutton6 is OK, but any clicks hereafter on the other enabled button, doesn't give a valid result or any result at all. I expect the focus or buttons state could be part of the problem, but I'm not sure. Have tried different things but with no luck.

Can anyone get an idea to solve this problem, then I'll be very happy. Or any other approach to solve the issue will be welcome.

The test below consists of a userform1 and 6 commandbuttons (1 to 6)

'---------------------
' Global dims
Dim i, x As Integer
Dim btLength As Integer
Dim ctrl As Control
'----------------------

Public Sub commandbutton5_Click()
    checkButton ("000120")
End Sub

Public Sub CommandButton6_Click()
    checkButton ("101122")
End Sub

Public Function checkButton(btString As String)

Set ctrl = UserForm1.Controls(msoControlButton)
On Error Resume Next

btLength = Len(btString)
    For i = 1 To btLength
        x = Mid(btString, i, 1)

            For Each ctrl In UserForm1.Controls
                ctrl.SetFocus
                    If ActiveControl.Tag = i Then
                        If x = 0 Then
                            ActiveControl.Visible = False
                            ActiveControl.Enabled = False
                        ElseIf x = 1 Then
                            ActiveControl.Enabled = False
                            ActiveControl.Visible = True
                        ElseIf x = 2 Then
                            ActiveControl.Visible = True
                            ActiveControl.Enabled = True
                        End If
                    End If
            Next ctrl
    Next i
Reply With Quote
  #2 (permalink)  
Old January 19th, 2005, 04:38 AM
Authorized User
 
Join Date: Jun 2003
Location: Cape Town, Western Cape, South Africa.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am not sure what it is that you are trying to do but why would you want to place buttons on top of each other to use/display a different one each time.

I would have written a routine that changes the caption of the button each time the condition was changed. At the same time this button caption was changed I would populate a variable (Integer) with a value that denotes the process to follow and in my click event I will validate this value and do the necessary - you could also check the .Caption property, it should do exactly the same.

Regards

Bruce
Reply With Quote
  #3 (permalink)  
Old January 19th, 2005, 08:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I see what you mean. Only I think I'll have to do a lot of coding, which is what I want to avoid.

Placing the buttons on top of each other is a question of space and design.

Disabling visible/enabled is used e.g. when in edit mode I don't want an Add or certain other buttons to be available.

If I could make my function work, it could be reused elsewhere only by creating the btString.

Regards Birger

PS! The above example is only used to test the technique. I'm to use it in an application where you have the possibility to edit, add and remove text.
Reply With Quote
  #4 (permalink)  
Old January 19th, 2005, 09:45 AM
Authorized User
 
Join Date: Jun 2003
Location: Cape Town, Western Cape, South Africa.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I don't think that you will do half as much coding for changing the caption than you would for hiding/showing (Visable property [True : False]) as you would for just changing the caption.

I have a program where I use one dialogue to search between Estimates/Quotes/Job Tickets/Clients/Staff etc.. and in the menu I allow the user to select wgat they want to do and then bring up the same form. However after the selection in the menu was made I change the caption on the form, set variable (eg: intOptSelected) and then present the form to the user to specify customer number/Staff number/ quote number/ etc...

When the user press search I execute a routine that looks something like this:

    Call Search_Routine(intOptSelected)

In the public sub search routine I then do this:

    Public Sub Search_Routine(Search_ID as Integer)

       Select Case Search_ID
         Case 0
           'do code
         Case 1
           'do code
         Case 2
           'do code
         Case n
           'do code
       End Select

    End Sub


Hope this helps, I think that this is much cleaner than your attempt.

Bruce


Reply With Quote
  #5 (permalink)  
Old January 19th, 2005, 05:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Bruce and thanks for helping.

Sometimes I can be very stubborn, and I think this is one of those times. I've got some good results using a class and I'm going to pursue that approach for some time now. If I don't succeed I certainly will fall back on your suggestions. I sure can end up with a lot of spaghetti code. Wouldn't be the first time. But again, thanks for your guidance.

Birger
Reply With Quote
  #6 (permalink)  
Old January 20th, 2005, 12:52 PM
Registered User
 
Join Date: Jan 2005
Location: Leatherhead, Surrey, United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Don't go yet Bruce! Can you help me on getting the LIFETIMES right for some controls on a user form please?

I have a form per worksheet, one worksheet per month. I run certain accounting month-end things from it, but don't know how to show that it's been done:

1) After a checkbox has been checked once, disable it.
2) At same time show textbox.visible nearby becomes true that says 'Done'.

...but to keep these settings on the form for the lifetime of that file, not only when the form's open, or when the workbook's open. Can you advise please? Many thanks, Tom
Reply With Quote
  #7 (permalink)  
Old January 20th, 2005, 04:22 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You have to save the settings somewhere. The easiest way to do so in to put the settings in a vacant area of a sheet, but this provides that you save your workbook before closing it. I expect you'll do that, otherwise you can save the settings in a file.

This example have 2 optionbuttons and a textbox in a userform. Keep the standard names for the controls and make sure you have a Sheet3:


Private Sub OptionButton1_Click()
    OptionButton1.Enabled = False
    TextBox1.Text = "Done"

    With Sheets("Sheet3")
        .Cells(1, 1).Value = OptionButton1.Value = False
        .Cells(1, 2).Value = TextBox1.Text
    End With
End Sub

Private Sub OptionButton2_Click()
    OptionButton1.Enabled = True
    TextBox1.Text = ""

    With Sheets("Sheet3")
        .Cells(1, 1).Value = ""
        .Cells(1, 2).Value = ""
    End With
End Sub

Private Sub UserForm_Initialize()
    With Sheets("Sheet3")
        If Not .Cells(1, 1).Value = "" Then
            UserForm1.OptionButton1.Enabled = False' or read --> .Cells(1, 1).Value
            UserForm1.TextBox1.Text = "Done" or read --> .Cells(1, 2).Value
        End If
    End With
End Sub

I've coded OptionButton2 to reset the settings and clear Sheet3.
Reply With Quote
  #8 (permalink)  
Old January 21st, 2005, 05:52 AM
Registered User
 
Join Date: Jan 2005
Location: Leatherhead, Surrey, United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Birger - many thanks for this quick answer - going to work on it now...cheers!
Reply With Quote
  #9 (permalink)  
Old January 28th, 2005, 10:45 AM
Registered User
 
Join Date: Jan 2005
Location: Leatherhead, Surrey, United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Birger - it worked great, thanks for taking the time!

Cheers, Tom;)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Chp.5 composite control properties studen77 BOOK: Professional ASP.NET 2.0 Server Control and Component Development ISBN: 978-0-471-79350-2 1 October 6th, 2006 03:35 PM
Change the properties of a textbox control? marksartwork ASP.NET 1.0 and 1.1 Basics 7 April 4th, 2006 04:55 PM
Referencing a user control properties trekmp General .NET 2 April 7th, 2004 08:40 AM
How to Handle a Winsock Control in better way tinku Beginning VB 6 0 March 8th, 2004 01:32 PM



All times are GMT -4. The time now is 05:34 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.