I'm trying to write a procedure that will enable button on a sheet (and colour it red), but ONLY when changes are made to cells in column E of that sheet (from row 10 to the bottom of the used-range).
However, at present, when I edit a cell in column E, nothing happens to the button (it is currently grey and disabled).
I've tried using Microsoft's VBA online help, and also my bible (Excel VBA for dummies) but haven't found any clues so far. Can anyone help?
I've written the code in the sheet, rather than a separate module. And I've done it as a sheet-change procedure, as follows: -
Private Sub Worksheet_Change(ByVal Target As Range)
-
-
Dim FirstRow As Double
-
Dim TotalRows As Double
-
Dim Row As Double
-
-
TotalRows = Worksheets("Calculation").UsedRange.Rows.Count
-
FirstRow = 10
-
-
For Row = FirstRow To TotalRows Step 1
-
If Target.Address = Worksheets("Calculation").Cells(Row, 5) Then
-
CaAdjustmentButton.Enabled = True
-
CaAdjustmentButton.BackColor = "&H0000000FF&"
-
Else: Exit Sub
-
End If
-
Next Row
-
-
End Sub
-
-
4 5224
I'm trying to write a procedure that will enable button on a sheet (and colour it red), but ONLY when changes are made to cells in column E of that sheet (from row 10 to the bottom of the used-range).
However, at present, when I edit a cell in column E, nothing happens to the button (it is currently grey and disabled).
I've tried using Microsoft's VBA online help, and also my bible (Excel VBA for dummies) but haven't found any clues so far. Can anyone help?
I've written the code in the sheet, rather than a separate module. And I've done it as a sheet-change procedure, as follows: -
Private Sub Worksheet_Change(ByVal Target As Range)
-
-
Dim FirstRow As Double
-
Dim TotalRows As Double
-
Dim Row As Double
-
-
TotalRows = Worksheets("Calculation").UsedRange.Rows.Count
-
FirstRow = 10
-
-
For Row = FirstRow To TotalRows Step 1
-
If Target.Address = Worksheets("Calculation").Cells(Row, 5) Then
-
CaAdjustmentButton.Enabled = True
-
CaAdjustmentButton.BackColor = "&H0000000FF&"
-
Else: Exit Sub
-
End If
-
Next Row
-
-
End Sub
-
-
Hi
There are maybe a couple of thing, but I think this line
If Target.Address = Worksheets("Cal culation").Cell s(Row, 5) Then
perhaps should be
If Target.Address = Worksheets("Cal culation").Cell s(Row, 5).Address Then
However, I think this may be better ?? - Private Sub Worksheet_Change(ByVal Target As Range)
-
-
Dim FirstRow As Integer
-
Dim TotalRows As Long
-
-
TotalRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
-
FirstRow = 10
-
-
If .Column = 5 And Target.Row >= FirstRow And Target.Row <= TotalRows Then
-
CaAdjustmentButton.Enabled = True
-
CaAdjustmentButton.BackColor = 255
-
Else
-
CaAdjustmentButton.Enabled = False
-
CaAdjustmentButton.BackColor = -2147483633
-
End If
-
-
End Sub
Note the use of Integer/Long data types for Row counters !!
HTH
MTB
Thanks again for helping out, Mike. Not quite there yet, but maybe you'll know why?
If Target.Address = Worksheets("Cal culation").Cell s(Row, 5) Then
perhaps should be
If Target.Address = Worksheets("Cal culation").Cell s(Row, 5).Address Then
I tried this but it didn't seem to do the trick, so I figured I'd abandon my code and try out yours.
However, I think this may be better ?? - Private Sub Worksheet_Change(ByVal Target As Range)
-
-
Dim FirstRow As Integer
-
Dim TotalRows As Long
-
-
TotalRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
-
FirstRow = 10
-
-
If .Column = 5 And Target.Row >= FirstRow And Target.Row <= TotalRows Then
-
CaAdjustmentButton.Enabled = True
-
CaAdjustmentButton.BackColor = 255
-
Else
-
CaAdjustmentButton.Enabled = False
-
CaAdjustmentButton.BackColor = -2147483633
-
End If
-
-
End Sub
Line 6 appears to have a big space at the end of it, before the .Row. The VBE didn't like that so i ran .Row onto the end of SpecialCells(xl CellTypeLastCel l)
But it still isn't quite working. If I make any changes in the sheet (not just in column E), I get an error message saying that .Column (line 9) is an invalid reference.
I've tried changing the reference to say Activesheet.Col umn. Now if I make any changes in the sheet (not just in column E), I get the error message "Run-time error 438. Object doesn't support this property or method".
Any clues?
Note the use of Integer/Long data types for Row counters !!
Point taken, I'll watch out for this in future. Thanks
Hi again
There seems to be something spooky going on
I havd noticed to large gap between the ')' and .Row but could not eliminate it inside the code tags !!
What I hadn't noticed was the missing Taget that should be (definitly was when I pasted it!!).
So this is what it should be (maybe!). - Private Sub Worksheet_Change(ByVal Target As Range)
-
-
Dim FirstRow As Integer
-
Dim TotalRows As Long
-
TotalRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
-
FirstRow = 10
-
-
If Target.Column = 5 And Target.Row >= FirstRow And Target.Row <= TotalRows Then
-
CaAdjustmentButton.Enabled = True
-
CaAdjustmentButton.BackColor = 255
-
Else
-
CaAdjustmentButton.Enabled = False
-
CaAdjustmentButton.BackColor = -2147483633
-
End If
-
-
End Sub
No, the extra spaces are still there, but at least the missing Target is now there. That should eliminate the error, hopefully.
MTB
Yup, the missing target was the problem. I'd even figured it out myself just before you posted (!!!). The code now runs perfectly, and you are a star.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ange T |
last post by:
Hi there,
I'm having pain with the VB behind an Access form. The form is used to
create reports in Excel based on the details entered in the form. This
has always worked without error on my machine (NT4, Access 2k),
however as soon as I attempt to create anything on another machine
(NT4, Access 2k) which most users will be working from, I...
|
by: Rick Brown |
last post by:
After reading 30+ threads on the subject and implementing pertinent
changes I still have an instance of Excel that won't close.
I hope its due to my poor coding and someone can spot the error.
I've used Dev Avish's code as a starting point and the Sub does what I
want less the hanging instance.
Please help if you can, Rick
Public Sub...
|
by: Matthew Wieder |
last post by:
I have the following requirements:
Build a stand-alone C# application that asks the user to click in a cell
in an Excel spreadsheet, and then displays the address of that cell in
the C# application. It seems simple enough, but the problem I'm
encountering is as follows:
In order for the user to select the cell from Excel, they must first...
|
by: Rushi |
last post by:
Hi Friends,
I don't know how many times I have submitted this topic, but no one is
trying to answer me....please some one try to help me.
I have created an add in C#, which is popup a form on excel. Now my
query is, i have a tree control which contain some data. now when user
drag any item to excel cell at that time i want information...
|
by: Boris Condarco |
last post by:
Hi gurus,
I'm using excel 2000 to show data that comes from datagrid. The problem is
that for any reason the asp.net application maintains the excel open, even
though, i do close it. Besides, does anyone know any third party compononet
to write excel files without having it installed?
My code looks like:
Public Function toExcel(ByVal...
| |
by: Charles Law |
last post by:
Mr "yEaH rIgHt" posted the following link about a week ago in answer to my
question about removing event handlers.
> http://www.vbinfozine.com/t_bindevt.shtml
Following on from that post, the following issues still exist.
The article shows how to find methods on a receiver that match the pattern
OnXXXX given the sender. It loops through...
|
by: ridawg |
last post by:
Hey,
I have some code where I open up Excel then loop through several cases to
update several workbooks.
Basically something like this (not showing all the code just basic structure).
Try
appExcel = New Excel Application
Do While Counter <=2
Select Case Counter
|
by: JohnM |
last post by:
I can transfer from a query with
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Filenam",
CPath, True
I would like to use a form for the user to select and order data then export
th result. How do I set about this?
|
by: kadghar |
last post by:
Most of the times VBA is used with variables. Objects (such as worksheets, cells or databases) are only used when we read their properties (value, formula, font...) or we use a method (save, open...). But their events are rarely used, and mainly when working with MS Forms.
Excel has two very important object types: Workbook and Worksheet, which...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |