|
Because this feedback was a bit more specific and complex with code, we're posting it separately here to make sure those who need it can find it.
Feedback
Ian writes...
Hi Mark,
I was wondering if you may be able to help be solve this question I have. I wrote the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
'colours the intere row if word PAID appears
Dim icolor As Integer
If Not Intersect(Target, Range("J17:J398")) Is Nothing Then
Select Case Target
Case "TEST"
icolor = 42
Case Else
'Whatever
End Select
Target.Range("A1:E1").Interior.ColorIndex = icolor
End If
If I type the word “TEST” anywhere in the range J17:J398, the row A1:E1 will be coloured (in fact it is not A1:E1 but actually J17:L17). So comes my first question:
1: why is that I have to call it A1:E1, when it is actually J17:L17?
2. I actually want to colour H17:J17 so how do I do that?
I had a look at your web site but I got confuse with the 1Row & iCol business & could not make head or tails out of it.
Could you please give me a hand?
Thank You
Mark Thorpe, author of the Excel VBA: No Experience Necessary series, writes...
Hi Ian -
It's a bit confusing, but the notation Target.Range("A1") refers to the upper-left cell within the "Target" Range object , not the upper-left cell within the worksheet (which would be ActiveSheet.Cells.Range("A1:E1")). In this case, Target represents the cell which has changed. So when you color Target.Range("A1:E1"), you're actually coloring the cell which has changed, and the four cells to its right.
If you want to color the cell and the two cells to the left (i.e. H17:J17), you'll want to determine the actual row and column number of the cell which has changed, and use those values to indicate which cells should be colored. For example:
rownum = Target.Row ' row number of changed cell
colnum = Target.Column ' column number of changed cell
Cells(rownum, colnum - 2).Interior.ColorIndex = 42
Cells(rownum, colnum - 1).Interior.ColorIndex = 42
Cells(rownum, colnum).Interior.ColorIndex = 42
This code colors each cell individually.
Better:
rownum = Target.Row ' row number of changed cell
colnum = Target.Column ' column number of changed cell
Range(Cells(rownum, colnum - 2), Cells(rownum, colnum)).Interior.ColorIndex = 42
This code defines a new Range and colors the entire range.
I hope this helps.
Mark
Feedback
A subscriber writes...
RE: Excel VBA: No Experience Required - HTML Export
Mark
Good article!
I have a question, is it possible to save the text box inputs on a userform to a text file (or similar) and then have an option to reload this file?
I have a series of budgets in separate excel worksheets where users input data through a user form, it would be easy if the could save this data to file then reload it at a later time.
I dont want to store the data in a hidden workbook for security reasons.
Any help, or code would be greatly appreciated
Mark Thorpe, author of the above article, writes...
I have to say that this is the first question I've received from Tajikistan. (not that you win a prize or anything, unfortunately)
It's certainly possible to do what you're suggesting. What about the possibility of loading the data from a separate Excel file, rather than a text file? (separate, in the sense that it wouldn't be the same spreadsheet that contained the userform) That would be easier in terms of the coding, because you wouldn't have to write any special code for parsing out the text file when you reload it. Mark
Further reply...
Gday Mark
I have been working in Tajikistan for almost 2 years in the Ag sector, yep, I am a farm boy from australia. The best of Tajikistan.....people are very friendly.... the worst of Tajikistan, corruption that leads to the poverty. Working here can lead to extreme frustration as the corruption prevents normal work practices from taking place.
The issue I have with the storage of data is security, I dont wont clients to have access to other clients data. So which ever you can suggest, I have been thinking about this and thought it may be more secure to password allow file access as it seems to be relatively easy to overcome security in spreadsheets. Besides I like the programming challenge of unloading and reloading into other programs.
Once again any help would be greatly appreciated.
Further reply...
I can relate somewhat to your experiences. I spent two years in the U.S. Peace Corps (1990-1992), living in Cameroon, W Africa, teaching high school math and science. Also a pretty crazy place!
Sounds like you're doing good work over there, although I'm sure it feels like you're banging your head against the wall sometimes. I put together a very simple example (download here) of how you could save data from a userform to a separate Excel file.
It basically just reads a first name and last name from Cells A1 and A2 of a separate Excel file, displays them in a userform, and lets you also save the contents of the userform to an Excel file. Take a look and let me know if this helps you out. If you have any questions, please send them.
Mark
|
