Posts by yjoshi

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveWindow.Caption = "ABC System"
End Sub

Cheers :beergrin:

I am really surprised that this has remained untouched for so long!!! :o

Are you talking about the same file or different files created diffetently???

Is it on NT/2000 (actually should not make any difference :()

Hi Andy,

Welcome to the board, and with this first very interesting quote

I think i am understanding your problem a bit (just a bit)

You are creating an application, suing formulas (and not using VBA) very hard Andy

This will certainly require VBA.

If you can post a sample of what you have created, and also in the same sheet, list down all the things that you want to do, i will certainly try to help you.

If i try to start doing something now, it will be shooting in dark.

Please update, and help us to help you :cheers:

Tried my hard,

I thought taking the total of all the cells to 288 will help, but now it puts value 4.5 everywhere, violating the integer function.

I am also struggling with one quote which required a problem to be solved in integer mode

Will the conditional formatting sufficient???

here is the formula for conditional formatting:
=NOT(ISERROR(MATCH(C3,my_range,FALSE)))

I have also attached a worksheet.

Let us know if you need something different and also if you need some kind of script in VBA to do this job for you.

This is something that came to me naturally as i read your post.

Am i right in judging it???:beergrin:

I think you are struggling with $ sign which is inserted when you work with conditional formattin and select the range with mouse click.

E.G. IF you select A1 and go to conditional formatting and click on A1 to get the formula, it will enter "=$A$1>1" in the formula.

Now if you copy the format to 50 rows, all will be true or false based on A1.

To come over this, change the formula to "=A1>1" and then copy.

Or even, select fifty cells and go to conditional formatting with A1 as primary selection and enter the formula above, the formating will be made relative to the each cell as per your requirement.

Hope this works. Let me know if not. :cheers:

Roy & Dave,

Good Links!!

I was surprised to find that i was exactly on the same lines, an without reading it

Hi Casca,

Just attach a sample copy of the file.

Somehow i get a feeling that you want to save only one sheet and not the entire workbook (which can contain a lot of other data)

If the entire workbook is to be saved with different name, i believe, there will be lot of duplication.

ALSO:: Be careful: not to make too many copies of same data!!! can be pretty difficult for the management to trace the correct information. :nono:

Hi Howdy Honey,

Welcome d-board!!!

Please be a bit more specific in your posts.

Do you want to protect the cells based on some condition (value, format, position?) or specific ones???

If specific, then NateO's suggestion is excellent. If you want this to be done by way of some code, please update accordingly.

Attached sheet, somehow the sheet did not get attached last time. :puzzled:

Hi Colo, good one.

I have different approach.

Say if you want to have a summary of colored cells in the worksheets!!!
Then both the inputs should be ranges.

===========================================
Public Function Cnt_Color(ByVal which_color As Range, ByVal rng As Range) As Long

Dim lngCnt As Long, c As Range

For Each c In rng
If c.Interior.ColorIndex = which_color.Interior.ColorIndex _
Then lngCnt = lngCnt + 1
Next

Cnt_Color = lngCnt

End Function
===========================================

Also attached a sheet!!!

Note: Once you have pasted a function in a module as Public, it can be accessed from Fx which will also give you what parameters to pass.

Very confusing in deed.

The formula will remain the same.
=DATE(YEAR(A2),MONTH(A2)+24,DAY(A2))

IF you want to use some logical condition, please change the figure 24 instead of your logical formula.

Finally:::
1. You will have a base date
2. You know how many months to increase
3. Why you want to increase (major / minor) you can set a if formula.

I can help you with if formula, but not sure what you want.

May be, if this is getting tooooo much for you, send an email or catch me on AOL, (note that i am in india, so see times)

Thats all i can do, but surely wish to help you.
:cheers:

I take liberty to paste a long code here.

As per the requirement of perryco sent by U2U, he is handling 40 vehicles and the data needs to be entered in the appropriate sheets based on value entered in col D.

The following code will :
1. See if the value in col D is valid (1 to 40)
2. See if the required sheet is available?
3. If not add the sheet from sample sheet
4. Copy the data in appropriat place
5. Put a date time stamp in front of the data

******************************************
Private Sub Worksheet_Change(ByVal Target As Range)
'This code will check the value in D, based on the number
'will paste the data in the sheet pertaining to it.
Dim sht As String
Dim s As Worksheet
Dim avialable As Boolean
Dim decision As VbMsgBoxResult
avialable = False

'========= Part I : checking range and data entered =========
'This will check if the value changed is in D column
If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

'This will check if the value is numeric and within the range of 1 to 40

If Target.Count <> 1 Then
MsgBox "The changed cells are more than one, can handle one at a time :("
Exit Sub
End If

If IsNumeric(Target.Value) = False Or _
Target.Value < 1 Or _
Target.Value &gt; 40 Then Exit Sub

'This will create the sheet name to copy the data
sht = "Veh" & Target.Value

'========= Part II : checking if the sheet exists =========
'This will check if the sheet is available
For Each s In ActiveWorkbook.Sheets
If s.Name = sht Then avialable = True
Next

'This will create the sheet if not available and name as per the vehicle #
If avialable = False Then

decision = MsgBox("The Sheet " & sht & _
" is not available, do you want to add it?", vbYesNo)
If decision = vbNo Then Exit Sub

Sheets("sample").Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = sht
avialable = True
Sheets("Data").Select
End If

'========= Part III : Actual copy-paste and time stamping =========
Application.ScreenUpdating = False
Sheets("Data").Select
Target.EntireRow.Copy
With Sheets(sht)
.Activate
.Range("A65536").End(xlUp).Offset(1, 0).Select
.Paste
Application.CutCopyMode = False
End With
Sheets("Data").Activate
Target.Offset(0, 1).Value = "The record is added in Sheet : " & sht & " on : " & Now()
Target.Offset(0, -3).Select
Application.ScreenUpdating = True

End Sub

******************************************

Thanx to perryco for asking cristle clear question. This is one of really good codes i wrote for some time.

Anyone please take liberty to jump in and suggest changes

Also a file attached

Hi Casca,

Welcome to the board

This is what you want.

This code needs to be put in the Code for his sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E2:E100")) Is Nothing Then Exit Sub
    If Target.Value = "BK" Or Target.Value = "NA" Or Target.Value = "AM" _
       Or Target.Value = "SD" Then
    Target.Offset(0, 1).Value = Now()
    End If
End Sub

also attached the sheet for your help.

:coolrsvd:

Well said Dave.

I knew, but was lazy to retpe the code, and knew that you will give atleast one more alternative :eureka:

Mae: Don't be aftead of the code.
See, if you need to do things effeciently, VBA, that's the highway. It takes a bit of time to reach there, but once you are there, your speed will be really good.

Just right on the sheet in which you want to keep this autocorrect.
In the code window for this sheet, just paste the code, and it will work.

Regarding Formulas, yes it is very much possible.

Just paste
=IF(A1= "computer key 100","CH100","")
in cell B1 and be sure that it will not be autofilled.

See now you have 2-3 solutions, it depends on you what you are comfortable with

Dave,

Sorry to jump in your code,

but what has happened is that, the autocorrect works every-wher except A1 to A100, but he wants that it should work only for A1 to A100 ???

This way, will he be required to give range of all the cells to exclude, which can be quite nasty. In this case, he will have to give union("a101:A65536","B:IV")

What do you say???

Hi Roy,

I had suggested it as an alternative!!
(i know not very good, but was not sure of what he wants)

What i can guess by interactions is that,

he has one sheet on which there is some data in auto filter mode with some data hidden.

He wants to copy it to other sheet, including the hidden data and keep the filter live.

What i will suggest is that::
- copy the filter information in the variables in run
- deavtivate the filter
- copy and paste the data
- apply the filter back to the data as well as the copied data.

how do you think this about efficiency???
:flower:

How about this

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

If Target.Value = "CS100" Then Target.Value = "Computer System Code 100"

End Sub

No need for auto-correct :beergrin:

I got your problem!!

Try this!!!

Save it with password for edit.

Access the file from other files with the password.

It does work well. Let me know if it doesn't

investigating.....

(also have to work in office :smash: )
:beergrin: