Lock And Unlock With Macro.

This is probably an easy question for most of you, but it's killing me. I tried searching before I posted this, but the suggestions given didn't seem to work for me.

I need to look up data from a cell on one Excel sheet, compare it to a list on a 2nd sheet and when they equal, take a value from a different column on the 2nd sheet and drop it back on the 1st sheet. Since that's a horrible explanation, let me try explaining it this way:

Sheet 1
Column A has a long list of code type 1s
Column E has a long list of code type 2s

Sheet 2
Cell C2 has code 1
Cell E2 needs code 2

I know it should take an index/match function, but nothing I try is working. What I'm trying is the following formula in Cell E2 of sheet 2:

=INDEX('Sheet 1'!A:A,MATCH(C2,'Sheet 1"!E:E,0),5)

But all I get is no result. What's driving me bonkers is that if I copy everything over to a single sheet, that formula works fine.

I need to lock consecutive rows together before sorting. e.g. A1 contains a name and A2 is blank: B1 contains data related to name in A1 - so does B2, and so on. Therefore need to lock rows 1 and 2 together, then 3 and 4 together and so on, but sorting on the data in the first cell of the group e.g. A1

Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do:

We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.

So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.

The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.

I ran a search on the forums and found something similar, but not quite what I was looking for.

Any help?

Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do?

I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.

Thanks in advance!

We have a large (4,000+ rows) excel worksheet from a prior employee that I
would like to be able to use, but it is password protected and no one has the
password. Is there a way to copy or save it as a different file that will
not copy the password protection? It would save me a couple days of work if
it is possible.
Thanks

Hi there,

I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!

In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:

=Sheet1!A3 (or whichever cell it is)

That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).

If anyone could point me in the right direction, I'd be very very grateful. Thanks.

Hello

I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me.

I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc.

When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result.

I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before.

Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious.

There's about 50+ cells that need referencing and I got to get this done for work.

Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time.

Hey there,

I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.

Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.

I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.

I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.

Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.

I'll post up further comments as I am trying to work my way through it!

Thanks,

Jag

I have found similar posts on the forum, but nothing without use of a userform.

I have the following code:

Code:

 
Sub Development()
    Pass = InputBox("Please enter development password", "Password")
    If Pass = "XYZ" Then
        UnhideAll
    Else: MsgBox ("Password incorrect, please try again")
    End If
End Sub

This works, but I want to hide the text as it is entered.
Is there a way I can enter this as a property? i.e. inputbox.text.property = ... or something?? I've tried what seems intuitive, but nothing works.

Help!

Afternoon all,

This is what I want to happen but I have no idea how to do it.

If C14 = "IV" THEN E14 = Locked AND F14 = Unlocked
If C14 = "RV" THEN E14 = Unlocked AND F14 = Locked
If C14 = "AJ" THEN E14 AND F14 = Unlocked

Another thing I would like to make possible is for this to work throughout cells C14:C450, E14:E450 and F14:F450

Can anyone help me out on this?

Hello, what I would like to do is take a portion of a sheet..... So the cells I want to mirror are in the proposal sheet cells B32 x F51. And then mirror the exact duplicate copy to another sheet. The other sheet should have those same values at A20 X G39. What I am trying to do is have a sheet that has skus, prices, and so forth and then when I add,delete, merge, color, or do anything to the cell on the "proposal creation" sheet I want it to mirror only a specific cells to another sheet that is "salesman copy" that we will print to clients. So this way ont he proposal copy a salesman can insert rows or even change the color of the cells and then have it mirror exactly the same on the "salesman copy" which is what we will print for clients. Thank you.

Hi

I need to open a password protected workbook using VBA.

I've tried the code below but I still get prompted for a password.

Code:

Workbooks.Open Filename:="\\HOME\Working\Report.xls" _
        , Password:="xxxx"

How do I get it to open automatically???

Hi all.

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:

Sheets("WELCOME").Select
    Range("A1:N18").Select
    ActiveWindow.Zoom = True

By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

Anyone know how to do this ?

i have a worksheet with formulas in 15,000 cells; i have set the calculation to maunal so that you have to press F9 to calculate the sheet. \

sometimes, it takes 45 seconds to calc the whole sheet, other times, it takes 5 minutes. how do i clear the cache, or what do i have to do so that it calcs 45 seconds each time.

i don't want the application to think, it would be better if those 15,000 cells can be returned in a fraction-of-a-second. any ideas/tips? thanks.

I've been using Excel for years and had very few issues. However, I recently went into a spreadsheet to update it and was unable to select and enter data into an individual cell. When I click on a cell and try to enter #s nothing happens (my num lock is on). Then when I try to click into another cell it just highlights that cell, along with any other that I move my cursor over. Once I click on one cell I can't stop the highlighting from happening. I can't even click on anything in the toolbar. I am extremely confused, can anyone help?