none
need to create buttons and enable it upon a conditon RRS feed

  • Question

  • i jave one excelpage called "welcpme" in that I want two buttons and it shpuld be active if only if value is entered in another 4 cells else deactivate and protect sheet
    Saturday, April 1, 2017 6:34 AM

Answers

  • I received an attached file (Excel-with-VBA) from you.
    I've modified and send it via email.
    How about it?
    • Marked as answer by DEEPU1980 Monday, April 3, 2017 1:42 PM
    Monday, April 3, 2017 4:23 AM
  • Hi DEEPU1980, I'm afraid you seem to stop thinking. If the file is helpful, you should mark it as an answer and ask next questions. Regards, Ashidacchi
    • Marked as answer by DEEPU1980 Monday, April 3, 2017 1:43 PM
    Monday, April 3, 2017 8:45 AM

All replies

  • Hi DEEPU1980,

    In order to make a button not clickable, write CommandButton1.Enabled = False.
    to make a button not visible, CommandButton1.Visible = False.
    In order to make Sheet1 to be protected, Worksheets(""Sheet1").Protect.

    In short, if "another 4 cells" are Range("A1:B2"), write like this.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
          If Intersect(Target, Range("A1:B2")) Is Nothing Then
            CommandButton1.Enabled = True
            ActiveSheet.Unprotect
        Else
            CommandButton1.Enabled = False
            ActiveSheet.Protect
        End If
    End Sub
    Regards,
    Ashidacchi
    Saturday, April 1, 2017 8:37 AM
  • i created button using insert function shapes

    does it need any different  format

    Sunday, April 2, 2017 3:44 AM
  • Hi DEEPU1980,

    You did not make it clear, what buttons are. So, I suppose they were ActiveX controls, instead of Shapes.
    If you use a Shape as a button, add macro(VBA code) in the Shape.

    I need more information about your Excel file.  Could you share it via cloud storage such as Dropbox, OneDrive, etc.  When I get it, I will modify and share it.

    Regards,
    Ashidacchi
    Sunday, April 2, 2017 4:14 AM
  • give you mail id I can mail it to you
    Sunday, April 2, 2017 5:02 AM
  • Here's my email address:
    ash (dot) m314 (at mark) gmail (dot) com

    please change:
    (dot) > .
    (at mark) > @
    Sunday, April 2, 2017 5:31 AM
  • I have send that mail
    Sunday, April 2, 2017 7:18 AM
  • I have received your mail.
    It was transferred into "Junk mail box" and I could not get noticed.
    Please wait. I will watch it.
    Sunday, April 2, 2017 7:23 AM
  • ok done
    Sunday, April 2, 2017 9:15 AM
  • Hi DEEPU1980,

    I find that you already created a thread for the same issue and then you created a new thread.

    i need to lock and unlock buttons upon acondition

    if the issue is same then you not need to create a new thread for the same issue. you need to continue your conversation on the same thread.

    please confirm that both the issues are same and I will merge both threads.

    when you post the thread , try to describe the issue with as much as possible details.

    if you want to enable and disable shape button then you can use code below.

     ActiveSheet.Shapes("Button 163").Enabled = False 

    try to integrate this code to make it work.

    if you have any further issue then post your demo code here. so that we can see it , what exactly you are trying to do.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 3, 2017 1:14 AM
    Moderator
  • I received an attached file (Excel-with-VBA) from you.
    I've modified and send it via email.
    How about it?
    • Marked as answer by DEEPU1980 Monday, April 3, 2017 1:42 PM
    Monday, April 3, 2017 4:23 AM
  • Awesome Ash  excellent

    in the below code its comparing soct center and equipment descrition with master data

    I need to compare sap equipment# with master data and automatically fill costcenter and eqp description, I done it with vlookup(sapequipment#,Reuesst!A1:c97626,2,false) but when new equmentsap# needs to enter we need to emter it in vlookup if its false it will show #n/a if data not found I need these to be blank ,

    can we use the shapes instead of buttons which is in my attached sheet  and can we use the same code for thati

    now some rows we are hiding upon a condition in the first sheet I need also some rows to be hide and unhide in the MAINTANENCE SHEET I NEED ROWS 9-10 12 14 TO BE HIDED ELSE UNHIDE  case Is ="PSL"

      Rows("8:9").EntireRow.Hidden = False
        Rows("4").EntireRow.Hidden = False
        Rows("11").EntireRow.Hidden = False

        Select Case Range("I3").Value      
            Case Is = "PSL"                  MAINTANENCE SHEET I NEED ROWS 9-10 12 14 TO BE HIDED ELSE UNHIDE
                Rows("8:9").EntireRow.Hidden = True
                Rows("4").EntireRow.Hidden = True
                Rows("11").EntireRow.Hidden = True
                'Me.Protect Password:="secret"
            Case Else
                Rows("8:9").EntireRow.Hidden = False
                Rows("4").EntireRow.Hidden = False
                Rows("11").EntireRow.Hidden = False
                'Me.Protect Password:="secret"
        End Select
    End Sub

     ' ---[B] check:
        ' --- Cost Centre, Eq Description
        Dim findRange As Range
        Dim costCentre_isFound As Boolean
        Dim eqDescription_isFound As Boolean
        ' --- check if CostCentre found in master sheet
        Set findRange = _
            Worksheets("Equipment Master").Range("C2:C97626").Find(what:=Range("CostCentre"))
        If (findRange Is Nothing) Then
            costCentre_isFound = False
        Else
            costCentre_isFound = True
        End If
        ' --- check if Eq.Description found in master sheet
        Set findRange = _
                Worksheets("Equipment Master").Range("B2:B97626").Find(what:=Range("EqDescription"))
        If (findRange Is Nothing) Then
            eqDescription_isFound = False
        Else
            eqDescription_isFound = True
        End If
        ' --- buttons: Enabled=True/Flase
        If (value_isExist = True) _
            And (costCentre_isFound = True) _
            And (eqDescription_isFound = True) Then
            btn_MaintenanceRequest.Enabled = True
            btn_RoadCall.Enabled = True
        Else
            btn_MaintenanceRequest.Enabled = False
            btn_RoadCall.Enabled = False
        End If

    Monday, April 3, 2017 6:48 AM
  • Hi,

    an we use the shapes instead of buttons which is in my attached sheet  and can we use the same code for thati

    Yes, you can use shapes by adding macro in the shapes, as you did before.

    Why I used buttons instead of shapes is simply for my  readability in code. 

    Regards,
    Ashidacchi
    Monday, April 3, 2017 7:01 AM
  • whats the code for hiding the shapes

    Monday, April 3, 2017 8:20 AM
  • how to refer Maintannence sheet rows to unhide
    Monday, April 3, 2017 8:28 AM
  • Hi DEEPU1980, I'm afraid you seem to stop thinking. If the file is helpful, you should mark it as an answer and ask next questions. Regards, Ashidacchi
    • Marked as answer by DEEPU1980 Monday, April 3, 2017 1:43 PM
    Monday, April 3, 2017 8:45 AM
  • Hi DEEPU1980,

    it looks like you directly want the solution.

    you need to refer Excel Object Model and try to find the thing you want to achieve.

    try to learn object model's properties, methods.

    Object
    model (Excel VBA reference)

    and then if you stuck some where then let us know about that.

    we will try to give you suggestion to solve the issue.

    Regards

    Deepak



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, April 3, 2017 9:08 AM
    Moderator
  • i used shapes instead if buttons  , I tried with the below code its not working

    btn_MaintenanceRequest.Enabled = True
            btn_RoadCall.Enabled = True

    Monday, April 3, 2017 6:21 PM
  • Hi DEEPU1980,

    Thank you for marking my post as an answer.

    If you use shapes instead of buttons, you should change the name of objects for ".Enabled = True", i.e. use the name of your shapes, instead of button's name.

    Regards,
    Ashidachi
    Tuesday, April 4, 2017 2:23 AM