*Hi *
*
*
*I have developed a custom button with name "Only one applicable".After user
clicks that it does some functions.*
*i want this button to be disabled later.*
*
*
*AT ANY TIME USER SHOULD BE ABLE TO CLICK THAT BUTTON ONLY ONCE.LATER THAT
HAS BE GRAYED OUT.*
*
*
*HOW CAN THAT BE DONE I USED THIS CODE TO DEVELOP A NEW BUTTON*
*
*
*
*
*Dim ctlMenu1 As CommandBarControl*
* Set myCommandBar = CommandBars.Add(name:="APPLY XLSB(RULESET)",
Position:=msoBarFloating)*
* myCommandBar.Visible = True*
* Set myCommandBarCtl = myCommandBar.Controls.Add(Type:=msoControlButton)*
* With myCommandBarCtl*
* .Caption = "Only one applicable"*
* .Style = msoButtonCaption*
* .TooltipText = "Only one applicable)"*
* .OnAction = " Only one applicableSUB"*
* End With*
On Tue, Oct 12, 2010 at 3:33 PM, sudheer lolla <sudheer1...@gmail.com>wrote:
> Hi Siti Vi
>
> *Superbly coded....That was excellent..full praise you*
> Thanks for excellent reply
>
> Regards
> L SUDHEER
>
> On Tue, Oct 12, 2010 at 7:02 AM, siti Vi <villager.g...@gmail.com> wrote:
>
>> Hi Lolla,
>> check this code
>>
>> --------------------- the VBA code -------------------------
>> Dim *DelValue*
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> If Target.Cells.Count = 1 Then
>> If Not IsEmpty(Target.Cells) Then
>> *DelValue* = Target.Value
>> End If
>> End If
>> End Sub
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Dim *rB* As Long, *rC* As Long
>> If Target.Cells.Count = 1 Then
>> If Target.Column = 1 Then
>> If IsEmpty(Target.Value) Then
>> With WorksheetFunction
>> If .CountIf(Sheets("B").Range("B:B"), *DelValue*) > 0 Then
>> rB = .Match(*DelValue*, Sheets("B").Range("B:B"), 0)
>> Sheets("B").Cells(*rB*, 2).ClearContents
>> End If
>> If .CountIf(Sheets("C").Range("A:A"), *DelValue*) > 0 Then
>> rC = .Match(*DelValue*, Sheets("C").Range("A:A"), 0)
>> Sheets("C").Cells(*rC*, 1) = ClearContents
>> End If
>> End With
>> End If
>> End If
>> End If
>> End Sub
>>
>>
>> On Mon, Oct 11, 2010 at 12:40 PM, sudheer lolla <sudheer1...@gmail.com>wrote:
>>
>>> *Hi siti Vi*
>>> *
>>> *
>>> *Thanks for your reply .That was efficient*
>>> *
>>> *
>>> *But i had a problem the order of the values may not be the same in all
>>> sheets*
>>> *
>>> *
>>> *for example:if "17" is in row 17 in sheet "A" it may be in row 20 in
>>> sheet "B"*
>>> *Your code works effectively if the order is same.You are comparing with
>>> target.row.This may not be the same in all sheets.I will end up deleting the
>>> other value.*
>>> *
>>> *
>>> *How can we overcome this problem*
>>> *
>>> *
>>> *Regards*
>>> *L SUDHEER
>>> *
>>> On Sun, Oct 10, 2010 at 6:58 PM, siti Vi <villager.g...@gmail.com>wrote:
>>>
>>>> Hi Lolla,
>>>>
>>>> try this code (written in Module of *Sheet A)
>>>> *and let me know*
>>>>
>>>> Private Sub Worksheet_Change(ByVal Target As Range)
>>>>
>>>> If Target.Cells.Count = 1 Then
>>>> If Target.Column = 1 Then
>>>> If IsEmpty(Target.Value) Then
>>>> Sheets("B").Cells(Target.Row, 2) = Target.Value
>>>> Sheets("C").Cells(Target.Row, 1) = Target.Value
>>>> End If
>>>> End If
>>>> End If
>>>> End Sub
>>>>
>>>>
>>>>
>>>> kindest regards,
>>>> siti Vi
>>>> jakarta
>>>>
>>>>
>>>> *
>>>>
>>>> On Fri, Oct 8, 2010 at 8:10 PM, sudheer lolla <sudheer1...@gmail.com>
>>>> wrote:
>>>> >
>>>> > Hi guys
>>>> > I Have a column "xyz" which is there in every sheet of the workbook
>>>> > and the column number in sheet A is 1 ,sheet B is 2,sheet C is 1
>>>> > If i delete a value from that column "xyz" in sheet A,the deleted cell
>>>> has to be deleted from these B,C sheets
>>>> > But the vice-versa should not happen i.e Delete of a cell in column
>>>> "xyz" of sheet B or C should not delete from A
>>>> > For me sheet A is a base sheet
>>>> >
>>>> >
>>>> > "DELETE REFLECTS ALL SHEETS IS WHAT I NEEDED"
>>>> > I will be very thankful to this community if i get some answer.
>>>> > Thanks
>>>> > sudheer.L
>>>
>>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>>
>> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>>
>
>
>
> --
> sudheer.L
>
--
sudheer.L
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
