• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vba to read button caption based on condition

HeenaS

New Member
Hello there,

I have a button which needs to act based on caption of button.. below is my code so far..pls help.. getting error at Me function .. dont want to use useform..

Code:
Sub CommandButton1()
Dim xStr As String
Dim Model1 As String
Dim Model As String
Dim Model2 As String
Dim cb As CommandButton

'Let Me = cb
Set cb = Me.CommandButton1

If cb.Caption = "Do you want to convert to Title1?" Then
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Rows("15").EntireRow.Hidden = True
Sheets("Sheet3").Rows("10").EntireRow.Hidden = True
cb.Caption = "Do you want to convert to Title2?"

Else
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Rows("15").EntireRow.Hidden = False
Sheets("Sheet3").Rows("10").EntireRow.Hidden = False
cb.Caption = "Do you want to convert to Title1?"
End If
End Sub
 
Last edited by a moderator:

p45cal

Well-Known Member
Where is the code? Standard Code module, a sheet's code module, the ThisWorkbook code module, a Class Module, a UserForm codde module, somewhere in the Personal Macro workbook?
It matters because Me means something different depending on where the code is. You may not need to use Me at all, but some other qualifier perhaps.
Finally, where is the button? On a sheet or in a userform?

If you could attach a workbook it would answer a lot of questions.
 

HeenaS

New Member
the code is in a sheet's code module and button is also in the same sheet.. sorry I can not attach the workbook here because of confidentiality of the organisation.

Requesting help pls
 

Belleke

Active Member
can not attach the workbook here because of confidentiality
We don't ask that.
Mock up an example without confidential data. (for example, instead of using real names use Name1 Name2 and so on.)
 

p45cal

Well-Known Member
Mock up an example without confidential data. (for example, instead of using real names use Name1 Name2 and so on.)
In this case it's easier than that; blank appropriately named sheets, one with a button, one macro.
It would quickly answer such questions as how the code is called, is the button activeX or not, is it on one of the sheets the macro is acting on?
Why make things easy for solution-finders when you can make it hard?
I'll look at this later, hopefully with a file from the OP.
 

HeenaS

New Member
We don't ask that.
Mock up an example without confidential data. (for example, instead of using real names use Name1 Name2 and so on.)
In this case it's easier than that; blank appropriately named sheets, one with a button, one macro.
It would quickly answer such questions as how the code is called, is the button activeX or not, is it on one of the sheets the macro is acting on?
Why make things easy for solution-finders when you can make it hard?
I'll look at this later, hopefully with a file from the OP.


I am now able to do the required operation on click of button but now the challenge is, 1. I want the code of button to be in "code modules" whereas I have written it in "sheet module" and I want the code to be called in "thisworkbook module"...

For eq. CommandButton1_Click is my sub in sheet module which I want to be in code modules so that I can call it from this workbook module whenever user performs the click opertaion.. pls help
 

p45cal

Well-Known Member
I want the code of button to be in "code modules" whereas I have written it in "sheet module" and I want the code to be called in "thisworkbook module"...

For eq. CommandButton1_Click is my sub in sheet module which I want to be in code modules so that I can call it from this workbook module whenever user performs the click opertaion.. pls help
Specifically the ThisWorkbook module? Why? How would it be called from such a module?
It looks like the button is an ActiveX button where the event code has to be in its sheet - however there could be an involved solution involving a class module, or a simpler one if you're happy to use a Forms button instead of an ActiveX button to which you can assign code sitting in the ThisWorkbook code-module.

Finally, again
Why make things easy for solution-finders when you can make it hard?
I'll look at this later, hopefully with a file from the OP.
Perhaps I'll be as relaxed about helping you as you are about helping me to help you…
 

HeenaS

New Member
Specifically the ThisWorkbook module? Why? How would it be called from such a module?
It looks like the button is an ActiveX button where the event code has to be in its sheet - however there could be an involved solution involving a class module, or a simpler one if you're happy to use a Forms button instead of an ActiveX button to which you can assign code sitting in the ThisWorkbook code-module.

Finally, again
Perhaps I'll be as relaxed about helping you as you are about helping me to help you…
Attached file... can use form button as well but will hv to modify code and assign value which m not able to do so.. pls help
 

Attachments

Belleke

Active Member
I still don't understand your question.
But try to avoid select and/or Activate, it slows down your code and is
rarely needed

This
Code:
Sub CleatData()
    Sheets("Sheet1").Range("A2:D6").Clear
End Sub
does just the same as this
Code:
Sub CleatData()
Sheets("Sheet1").Activate
Range("A2:D6").Select
Selection.Clear
End Sub
 

HeenaS

New Member
I still don't understand your question.
But try to avoid select and/or Activate, it slows down your code and is
rarely needed

This
Code:
Sub CleatData()
    Sheets("Sheet1").Range("A2:D6").Clear
End Sub
does just the same as this
Code:
Sub CleatData()
Sheets("Sheet1").Activate
Range("A2:D6").Select
Selection.Clear
End Sub

This does not seem resemble with my query... M not using clear data anywhere :(
 

p45cal

Well-Known Member
HeenaS, whatever you do, when you've asked people help you, and they ask you some questions, do not even consider answering them under any circumstances; why would you make things easy when you can make them harder?

Attached has code in Module1 only. There are comments in there.
When you add a Forms button, assign the myCommandButtonClick macro to it.
 

Attachments

p45cal

Well-Known Member
If you want the macro to be called by clicking any one of several buttons AND to be able to be called from the Thisworkbook code-module there's going to be a problem: The code changes the caption on a button, if it's called from the ThisWorkbook module, which button do you want it to affect?

If, on the other hand, you want the code to reside in the ThisWorkbook code-module, you can just move it from Module 1 to the ThisWorkbook code-module. You might have re-assign the macro's new location to the buttons concerned.
 

HeenaS

New Member
There is only 1 button in entire workbook but cant guarantee if there is any addition in future... anyways.. as of now this seems fine... will let u know in case if changes r required... Thanks a lot for help....
 
Top