--------------------------------------------
On Mon, 3/6/17, Maatangi M. Karthik <maata...@gmail.com> wrote:
Subject: Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel
2010 with Datavalidation & VBA
To: "MS EXCEL AND VBA MACROS" <excel-macros@googlegroups.com>
Cc: schreiner_p...@att.net
Date: Monday, March 6, 2017, 6:30 AM
Hello
Paul,
Pls find the attached
Sample.xlsm file for your reference.
I did try the lock cells for certain cells but
that when the formula had to be computed, locked cells had
to be overwritten and that was not permitted.
If there is a possibility of
talking to explain things that I want to accomplish with the
excel, do let me know.
Otherwise, I will have to explain here and get
it done.
Thanks,
Maatangi
On Friday, 3 March 2017 18:27:55 UTC+5:30, Paul
Schreiner wrote:First
of all (last of all?):You can lock some cells and
leave others available for editing:- select all
cells- right click on the selection -
Select "Format Cells"- select
"Protection" tab- Make sure
"Locked" is checked.- click
"OK"
When the sheet is
protected, all cells with this "locked" attribute
will be.. um... "locked"...Now select the cells you want to allow users to
enter data into.then:-
Select "Format Cells"- select
"Protection" tab- Make sure
"Locked" is NOT checked.-
click "OK"
Now, depending on what
options you choose when you protect the sheet, the cells with the formulas
cannot even be
selected!while the user entry cells are
left unchanged.
If you want to be able to
copy and paste all the data INCLUDING the locked cells to
another sheet,make the appropriate
selection when protecting the sheet.
I think it
"SOUNDS" like you want to accomplish your other
items using Excel functions rather than VBA.(however, VBA is ALWAYS an option)
But it would
be a LOT easier for both of us if you could share a sample
file(even with "dummy"
data)
I'd be glad to help.
Paul------------------------------
-----------
“Do all the good you can,
By all the means you can,
In
all the ways you can,
In all the places you
can,
At all the times you can,
To all the people you can,
As
long as ever you can.” - John Wesley
------------------------------
-----------
On Friday, March 3,
2017 12:16 AM, Maatangi M. Karthik <maat...@gmail.com> wrote:
Hi there are a few more things I want from the
excel.
1. There are some
formulae in my excel sheet, I would like to convert them
to the backend so that the users cannot change them at any
cost
2. There are some fields that affect
these formulae in succession, that is,
two or three cell values together will make up the formula
for another
cell. I want to be able to capture the input event and the
formula
creation based on this input.
For example,
there is a field where a due date gets calculated. And this
column is dependent on 2 other columns' values. so I
want to capture those 2 columns' input events and invoke
the formula once they are filled.
3. In the
excel there are 3 fields, Tax,sales price with tax and sales
price without tax. At present I have given the option of
entering /
choosing the Tax % and entering the without tax price.
However, there
may be cases where the user will know only the with tax
price and the
tax %. this creates double work for them as they have to
compute the
without tax price and then paste it in this excel. I want to
avoid this
double work and give the option for the users to enter price
in either
of the columns and then be able to compute based on which
one is input.
4. Last query for now,
I tried Locking / hiding the formulae for certain fields;
however, they
don't take effect unless the excel sheet is protected
and if I protect
the sheet, then users are unable to edit / input their
values where
necessary. Is there a VBA workaround for this? Most of the
date fields have to be locked as well and should not be
allowed to be changed by users.
On Friday, 3 March 2017 01:02:22 UTC+5:30, Paul
Schreiner wrote:I deleted a
row and received the "Type Mismatch"
error.If you select "Debug", you'll
see that the offending line of code is: If Target.Value = "OrderStatus"
ThenPutting a "watch" on the Target
variable, you'll see that it isn't a single cell,
but an array of cells!
When you change multiple cells (even
by deleting, or inserting, or copying), then the Change
event is passed an range ARRAY rather than a single
range.
In your case,
you have a choice.You can add an if() statement
like: if (Target.count > 1)
then exit sub
Or, you can process each
cell in the target array like:
dim Targ as
rangefor each Targ in Target... (replace each occurrence of "Target"
with "Targ") ...Next
Targ
Paul------------------------------
-----------
“Do all the good you can,
By all the means you can,
In
all the ways you can,
In all the places you
can,
At all the times you can,
To all the people you can,
As
long as ever you can.” - John Wesley
------------------------------
-----------
On Thursday, March 2,
2017 12:33 PM, Maatangi M. Karthik <maat...@gmail.com> wrote:
I am absolutely new to
excel VBA.
I have a requirement to create an excel to maintain status
of orders
(there are about 6 / 7 order statuses (2 of which are
OrderStatus(no
status), Enquiry and taken as samples here ) and based on
each status, a
specific set of actions have to be performed. I have
created the excel
in which there are multiple columns & rows, some of the
columns have
data validations either from a reference sheet or entered
as list in
the Data validation part and some have formula
references.
What little coding I could understand, I have done that
based on my
teeny weeny bit of knowledge + the ideas that I got from
checking on
various websites. As a result, I am not sure if I have a
robust code,
although, I have some piece of coding to perform certain
actions based
on values change in certain columns.
Following are the issues that I want help with:
When inserting / deleting row / rows, get error
"Type Mismatch error 13"
The same error appears while certain columns are
extended down to the cells.
Same error while cut copy pasting a row
Although the run time error occurs, the action is
partially completed
Earliest response would be much appreciated.
My sample excel is attached
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you
wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official
Facebook page of this forum @ https://www.facebook.com/
discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles,
like Please Help, Urgent, Need Help, Formula Problem, Code
Problem, and Need Advice will not get quick attention or may
not be answered.
2) Don't post a question in the thread of another
member.
3) Don't post questions regarding breaking or bypassing
any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not
allowed.
NOTE : Don't ever post confidential data in a workbook.
Forum owners and members are not responsible for any
loss.
---
You received this message because you are subscribed to the
Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails
from it, send an email to excel-macros...@ googlegroups.com.
To post to this group, send email to excel-...@googlegroups.com.
Visit this group at https://groups.google.com/
group/excel-macros.
For more options, visit https://groups.google.com/d/
optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you
wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official
Facebook page of this forum @ https://www.facebook.com/
discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles,
like Please Help, Urgent, Need Help, Formula Problem, Code
Problem, and Need Advice will not get quick attention or may
not be answered.
2) Don't post a question in the thread of another
member.
3) Don't post questions regarding breaking or bypassing
any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not
allowed.
NOTE : Don't ever post confidential data in a workbook.
Forum owners and members are not responsible for any
loss.
---
You received this message because you are subscribed to the
Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails
from it, send an email to excel-macros...@ googlegroups.com.
To post to this group, send email to excel-...@googlegroups.com.
Visit this group at https://groups.google.com/
group/excel-macros.
For more options, visit https://groups.google.com/d/
optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you
wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official
Facebook page of this forum @ https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles,
like Please Help, Urgent, Need Help, Formula Problem, Code
Problem, and Need Advice will not get quick attention or may
not be answered.
2) Don't post a question in the thread of another
member.
3) Don't post questions regarding breaking or bypassing
any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not
allowed.
NOTE : Don't ever post confidential data in a workbook.
Forum owners and members are not responsible for any
loss.
---
You received this message because you are subscribed to the
Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails
from it, send an email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
el mai important act legislativ care a stat la baza edificiului politic si
institutional al Romaniei a fost Constitutia din 28 martie 1923. O constitutie
noua era ceruta imperios de transformarile aparute in societatea romaneasca
dupa Marea Unire.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
