Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| 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
 
Old January 21st, 2005, 02:39 AM
Authorized User
 
Join Date: Jan 2005
Location: Kuala Lumpur, Fed. Territory, Malaysia.
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default Active X -Commandbuttons

Hello Friends and educators!

  I am a newbie to Vba Excel.Is there anyone out there able to guide me on this?
  I use a CommandButton (Active X Control)to change data on cell
A1 . The 1st time I click the button, cell A1 displays "200"; and changes its button caption to , say, "Link".
     The 2nd time I click again cell A1 displays "300", and changes its button caption to,say, " More".
    The 3rd time I click again cell A1 displays "400", and changes the button caption to "Enough".
    The 4th time I click again cell A1 would display "Exceed Limit", and changes the button caption to "Back"

   Appreciate all help and guidance.
:)
CHIN
 
Old January 21st, 2005, 06:29 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Chin,

two questions,

1 Before you click the button what is in cell A1?
2 what do you want to happen after the user clicks the button a fifth time?

Cheers

Matthew

 
Old January 21st, 2005, 06:36 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is some code to do what it is I think you want...

in the VB editor open the code page behind Sheet1 (of whichever sheet contains the commandbutton), remember this code page is not an inserted module but within the Excel object shown as sheet1.

Ensure the button on sheet1 is called CommandButton1, paste this code in the above code page...

Private Sub CommandButton1_Click()
    Select Case Range("a1").Value
        Case ""
            Range("a1").Value = 200
            CommandButton1.Caption = "Link"
        Case 200
            Range("a1").Value = 300
            CommandButton1.Caption = "More"
        Case 300
            Range("a1").Value = 400
            CommandButton1.Caption = "Enough"
        Case 400
            Range("a1").Value = "Exceed Limit"
            CommandButton1.Caption = "Back"
        Case Else
            Range("a1").Value = ""
            CommandButton1.Caption = "Link"
    End Select
End Sub



cheers

Matthew

 
Old January 24th, 2005, 03:29 AM
Authorized User
 
Join Date: Jan 2005
Location: Kuala Lumpur, Fed. Territory, Malaysia.
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, Matthew,

          Thanks for your effort. Actually, I would begin with an empty cell ("A1").
          When the last click(the 5th one ) is executed, it should go back to the original position before I click the first time.
          I shall test your codes if ever you have any ideas to go about it and see whether it works on my excel 2000.
         You are just wonderful and discerning.

CHIN




Quote:
quote:Originally posted by mjppaba
 Hi Chin,

two questions,

1 Before you click the button what is in cell A1?
2 what do you want to happen after the user clicks the button a fifth time?

Cheers

Matthew

 
Old January 24th, 2005, 02:26 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

nice lips..

;)

M





Similar Threads
Thread Thread Starter Forum Replies Last Post
Active X Controls Slicemahn Access VBA 1 June 12th, 2006 12:59 PM
Commandbuttons in Reports DaDeViL Access VBA 2 August 19th, 2005 08:13 AM
Active X razi BOOK: Beginning Visual C++ 6 0 July 22nd, 2005 01:37 AM
About Active Directory apalmero VB How-To 1 April 8th, 2005 04:06 PM
Protection and commandButtons bahachin Excel VBA 3 March 4th, 2005 09:27 AM





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