Search results

I just created two more arrays to finish off rest of the cells in the sheet. Just had to create new RngToProtect for each array. Your array works. Thanks so much for your help!

Hi, I tried to add more cells and range in the code below and it gives me an error message. It seems it has a cap on how many cells can be added into it? Run-Time Error '1004': Method 'Range' of object'_Worksheet'failed' >>> as written >>> use correct CODE -tags <<< Set RngToProtect =...

I will not be able to use the lock cell feature that comes with Excel because the protected field contains calculation. Hence its value changes as the user enters data into other yellow field. I've tried and it doesn't work. The array you provide still doesn't work for me. I am still able to...

I don't have any preference on the direction it should bump as long as all the protected cells are not being selected. That way user will not be able to edit the value of those cells. My original codes pumps the selection downward. Then it seems there are too many rows. Therefore it gives me the...

Is there any other way to do this? I can see you put my codes into an array. My original codes bump the selection to the next unlocked cell in the sheet. But it is not working now due to the error message. It seems my code can only protect the cells in row 1 through row 95. But I have total 114...

Hi, Thanks for your reply and providing the codes. However, your code would basically bump the selection to a cell in the row below. Unfortunately some of those cells in the row below are the ones that I want to protect as well.

Hi Guys, I get following error message when I try to run the codes below. There are total 114 row in the actual sheet. I am only allowed to post some due to the limitation of 10000 characters per message. First error message: Run Time Error 28, Out of Stack Space Another error message: Run...

I think I made it work. :)

Hi guys, I don't get the cell color change when I selected desired value in another cell. Please help!! I think I need to move the second if condition into the first if statement to create if condition and if condition 2 But not sure how to do it. Private Sub Worksheet_Change(ByVal Target...

Sorry. I was not clear in my first post. Basically I have VBA writing data to those cells. So it gives me error message if I lock those cells. So the locking those cells will not work with my VBA. Anyway I am trying to prevent the cells being selected by the user using VBA. I have the...

Hi All, I have bunch of cells that contains formula and its value changes based on user input of other cells. Therefore, I will not be able to lock those formula active cells. How can I disable select of those formulated cells using VBA? I've tried to use the protect sheet feature within...

Hi Marc, Thanks for posting the codes. I actually make it to read all the data in the table at each user input field. So it kind serves as a worksheet refresher whenever the user changes a data in the input field. It seems to be working. I just created the refresh codes in a sub procedure and...

Hi All, I've attached a file. The file basically is an user form that pulls the data from the table at the bottom and display them in a form. But the form won't show the updated data after I updated the data in a cell of the table. For example, I change the patient name in the table. The new...

Hi All, I have created an user form which takes user input data and populated to a table at the bottom of the worksheet. I use VBA to make it work. Now I have created some formulas within the cells of the table. But the newly calculated data don't get to put in the form. I need to refresh the...

Is there a way to check the actual size of the procedure?

Just checking. Is there max number of sub procedures that I can write per worksheet? I will have 150 sub procedures for my worksheet and will calling each of them from the main private sub.

Hi Guys, I was able to create sub procedure for each data input field and call it from the private sub procedure. It seems to be working. Let me see how much KB it can save me in the main sub.

Hi Hui, Thanks for your reply. I've attached the sheet and the yellow highlighted fields are requiring user input. I have not completed creating the table to connect the user form yet. Basically whatever the expense that an user enters in the yellow highlighted field will get to populated into...

I think the user input fields are taking up a lot of space. Is there a short cut to those codes below? I'm going to try Mark's recommendation to see how much space I can save with his version of the code. 'E86- Other Materials Per Site Unit Cost 2 ElseIf Target.Address = "$E$86" Then Dim...

Ok, I see this is something impossible to achieve with VBA.

@ vletm Unfortunately reducing number of data fields are not an option. @ Marc L, I have only pasted partial codes. Here comes the full code. It is too big to put in one procedure. Is there way to put them into multiple sub procedures? There are If statements which makes things a little...

Hi Guys, How to break this huge procedure into multiple small ones? The codes are much much bigger. I am copying and pasting partial codes here. Basically it is a user form that display the data from a table and the program takes some user input data from the form and populate them back into...

I need to temporarily ignore those codes and will need them later. I know I can put ' at the beginning of each code line manually. But it would be a pain to do that for hundred lines of code.

Hi All, I got an error message stating "Next Area" is not defined when I used the following codes. If statement code line 1 code line 2 GoTo Next Area 'so I want to ignore code line 3 and 4, go straight to Else then. code line 3 code line 4 Next Area Else Then execute codes...

For some reason the step into feature doesn't work for me. I inserted the break point in the code and tried click on "step into" under Debug or by pressing "F8" key. It gives me a beep sound.