Cell recording & overwriting

[gsheets]

cell A3 contains a drop down list of student names.
say, William is the 1st student listed on cell A1.
when William is selected his math grade shows up on cell B3 & his English grade on cell C3

I figured out how to make cells B3 & C3 change according to selected student from cell A3. All data comes from the Database Table, which is locked/unedittable.

Here is how my sheet looks like

-------------------------------------------------------------------
...............................STUDENT GRADE REPORT
STUDENT...........................MATH GRADE..........ENGLISH GRADE
William (Drop Down List)...........3.5.......................3.6

. Magic Button .



.......................DATABASE TABLE
STUDENT.........MATH GRADE.......ENGLISH GRADE
William.................3.5.....................3.6
Christina...............3.9.....................4.0
Valentino..............4.0....................3.2
Elena...................4.0....................4.0

-------------------------------------------------------

Now say Elena's Math grade changed. I select her from the drop down list in cell A3 & change her math grade on cell B3 to 3.9. The, I push a (magic) button that overwrites her math grade on the corresponding cell in the Database Table. So when I re-pull here name again, the Math Grade (cell B3) reflects saved grade from Database Table.

where I need help is that "magic button".

Does this makes sense? Or am I over complicating it?

[Carim]

Hi,

It is not a Magic Button ... it is a simple formula ...
Vlookup()
Press F1 to get complete explanation and examples ...

[Bryan Hessey]

cell A3 contains a drop down list of student names.
say, William is the 1st student listed on cell A1.
when William is selected his math grade shows up on cell B3 & his English grade on cell C3

I figured out how to make cells B3 & C3 change according to selected student from cell A3. All data comes from the Database Table, which is locked/unedittable.

Here is how my sheet looks like

-------------------------------------------------------------------
...............................STUDENT GRADE REPORT
STUDENT...........................MATH GRADE..........ENGLISH GRADE
William (Drop Down List)...........3.5.......................3.6

. Magic Button .



.......................DATABASE TABLE
STUDENT.........MATH GRADE.......ENGLISH GRADE
William.................3.5.....................3.6
Christina...............3.9.....................4.0
Valentino..............4.0....................3.2
Elena...................4.0....................4.0

-------------------------------------------------------

Now say Elena's Math grade changed. I select her from the drop down list in cell A3 & change her math grade on cell B3 to 3.9. The, I push a (magic) button that overwrites her math grade on the corresponding cell in the Database Table. So when I re-pull here name again, the Math Grade (cell B3) reflects saved grade from Database Table.

where I need help is that "magic button".

Does this makes sense? Or am I over complicating it?

Hi,

The 'button' would be one from the Control Toolbox (View, Toolbars, Control Toolbox) and the VB Code behind the button would need to:

set screenupdating to false,
unprotect the figures sheet,
update the required figures,
re-protect the sheet,
restore the VLookup formula that you overwrote entering the figure
set screen updating to true.

hth
---

[gsheets]

hi guys, thank you for your inputs
I need few days to implement your ideas

I did not get the lst part: "Si fractum non sit, noli id reficere." ?
Thank you, again

[Bryan Hessey]

hi guys, thank you for your inputs
I need few days to implement your ideas

I did not get the lst part: "Si fractum non sit, noli id reficere." ?
Thank you, again

if it ain't broken, then don't fix it.

One thing you will need to decide is whether to have the password within the macro, or to ask for the password each time (the former is usual).

hth
---
added, the code would be something like:

Please Login or Register  to view this content.

----

[gsheets]

Hi Brian, thank you for your help

I have read about Application.EnableEvents & Application.ScreenUpdating; as I understand these lock other functions to let the macro do the job & then unlock them as soon as conditions are met.

One item I encountered (it my not be relevant to this original post) is that whenever I double click any unlocked cells, cell values become blank.
Do you think that prompting a user form is a good idea whenever any of those cells are double clicked?
What would be a function for that.
For instance, whenever either of unlocked cells B3 or C3 is double clicked their value becomes blank.
?Are my funtions/operators are wrong:
---------------------------------------------------------------
if activecell.select=cell("b3") then pop-up UserForm to fill cell b3
(processes indicated in your last post - thank you!)

otherwise (if cell("b3") is not selected) then continue
---------------------------------------------------------------
I have tried a number of command variations, it seems I am having problem with syntax & if the both side of equations are of the same type

[Bryan Hessey]

Hi Bryan, thank you for your help

I have read about Application.EnableEvents & Application.ScreenUpdating; as I understand these lock other functions to let the macro do the job & then unlock them as soon as conditions are met.

EnableEvents stops any further triggers whilst this trigger is completing it's task, required if the task wil operate on the trigger range. Screenupdating simply supresses screen output.

One item I encountered (it my not be relevant to this original post) is that whenever I double click any unlocked cells, cell values become blank.
Do you think that prompting a user form is a good idea whenever any of those cells are double clicked?

that depends on what you are doing to cells B3 or C3

What would be a function for that.
For instance, whenever either of unlocked cells B3 or C3 is double clicked their value becomes blank.
?Are my funtions/operators are wrong:
---------------------------------------------------------------
if activecell.select=cell("b3") then pop-up UserForm to fill cell b3
(processes indicated in your last post - thank you!)

otherwise (if cell("b3") is not selected) then continue
---------------------------------------------------------------
I have tried a number of command variations, it seems I am having problem with syntax & if the both side of equations are of the same type

to show a small amount of 'would-be' code out of context does not make it possible to answer your question.

How did you get to be performing this code? - is it a macro or an Event-triggered cell entry, or an event triggered from calculation or button?

the words 'if activecell.select' are not real, a cell becomes the activecell because of a select.

If ActiveCell.Address = "$B$3" Then

would be more realistic, but not on a macro nor calculation unless pre-set, more useful on a Change trigger or a Button, but a button would know it's related cell.

hth
---

[gsheets]

I think I am getting your comments about the events.

How did you get to be performing this code? - is it a macro or an Event-triggered cell entry, or an event triggered from calculation or button?

The idea is not to allow the double-clicked cell's (B3) value to disappear, so when cell (B3) is selected, user is prompted a "UserForm" to fill out (basically to enter a new value for cell B3). Also, those events you mentioned come to play to freeze everything except whats in VBA code

the words 'if activecell.select' are not real, a cell becomes the activecell because of a select.

If ActiveCell.Address = "$B$3" Then

would be more realistic, but not on a macro nor calculation unless pre-set, more useful on a Change trigger or a Button, but a button would know it's related cell.

the command line worked! Now i will be attempting to build a data gathering UserForm.

sorry, but i may have to bug you again
THANK YOU!

[gsheets]

Is there a way to keep cells value, even when that cell is double clicked.
However, as a new cell value is entered to keep the new value?

[Bryan Hessey]

Is there a way to keep cells value, even when that cell is double clicked.

double-clicking a cell would not normally remove a cell's value, is the value removed by your code? if so, what is the purpose of the code?

However, as a new cell value is entered to keep the new value?

Excel will normally retain a value entered into a cell, I think the 'confused' word is correct, I have no real idea what you are asking.

To double-click a cell will not affect the cells value unless there is some code to change the cell, what code do you have? - or what is the purpose of double-clicking the cell?

more details?

---

[gsheets]

You are right again - those blanking cell did have a formula in them, not just a static data.

please find attached the file with password of "password"
since xls files are not allowed for an upload, i had to rename the file with txt extension & still was able to open it using excel even without renaming to xls
I think I am progressing thanks to you

[Bryan Hessey]

Hi,

your code is not performing correctly, after you trigger F6 and respond the value is put to F6 but the macro stops there and does not re-set Events.

you can then edit F6 (and any cell), and selecting F6 no longer triggers the inputbox.

this seems to happen until you enter a value in F6 and then reset the Events.

Where did you get the 'selectionchange' from? - is this a legitimate use of this event? - it doesn't look correct.

---

[gsheets]

Hi,

your code is not performing correctly, after you trigger F6 and respond the value is put to F6 but the macro stops there and does not re-set Events.

you can then edit F6 (and any cell), and selecting F6 no longer triggers the inputbox.
---

You right, selecting F6 no longer triggers the inputbox if F6 is already the active cell; selecting A1 as an active cell as soon as that submacro is done should solve the problem. Is this a valid formula - active.cell = cell ($a$1") ?

Where did you get the 'selectionchange' from? - is this a legitimate use of this event? - it doesn't look correct.
---

I found it somewhere is the forum & have no idea what it means

-------------------------------------------
I do not see any problems in codes related to my F6 triggering (other than mentioned above): I think it is doing:
1. when cell F6 is selected, it locks everything else but the pop-up inputbox.
2. if user inserts anything into inputbox, that number is recorded to cell F6 when 'Ok' button is clicked. Otherwise (if cancel is clicked), cell F6 is reset to its original value by reinserting VLookup formula.
3. Until "Record Data" command button is clicked the changes are not apllied to range "DataBase"

I saw some redundancy & shrinked the code abit & added inputbox submacro to Cell E6.

Now, the problem I have is with finding syntax for the left side of the formular that assigns a new value to a corresponding row of the 'Database' range whenever "Record Data" command button is clicked. in other words,

Range("Database", row#=$F$2, iColumn=E6).Value = Range ("$E$6")
Range("Database", row#=$F$2, iColumn=F6).Value = Range ("$F$6")

???
eventually, i am planning to create a loop sequence for the assignment - since nubmer of subject make incread ( math, lit, lang, science, pe,...):
So, I was thinking if creating a range from E6, F6, G6, H6,... cells, would a good idea? so, it shows like:

Do Range("Database", row#=$F$2, iColumn=i6).Value = Range ("$i$6")
until i<= n

where 'i' is a variable and 'n' is an amount of subjects

i have attached a new code. the workbook is with txt ext since I cant appload a file with xls extension. password is the same - password

[Bryan Hessey]

You right, selecting F6 no longer triggers the inputbox if F6 is already the active cell; selecting A1 as an active cell as soon as that submacro is done should solve the problem. Is this a valid formula - active.cell = cell ($a$1") ?


I found it somewhere is the forum & have no idea what it means

-------------------------------------------
I do not see any problems in codes related to my F6 triggering (other than mentioned above): I think it is doing:
1. when cell F6 is selected, it locks everything else but the pop-up inputbox.
2. if user inserts anything into inputbox, that number is recorded to cell F6 when 'Ok' button is clicked. Otherwise (if cancel is clicked), cell F6 is reset to its original value by reinserting VLookup formula.
3. Until "Record Data" command button is clicked the changes are not apllied to range "DataBase"

I saw some redundancy & shrinked the code abit & added inputbox submacro to Cell E6.

Now, the problem I have is with finding syntax for the left side of the formular that assigns a new value to a corresponding row of the 'Database' range whenever "Record Data" command button is clicked. in other words,

Range("Database", row#=$F$2, iColumn=E6).Value = Range ("$E$6")
Range("Database", row#=$F$2, iColumn=F6).Value = Range ("$F$6")

???
eventually, i am planning to create a loop sequence for the assignment - since nubmer of subject make incread ( math, lit, lang, science, pe,...):
So, I was thinking if creating a range from E6, F6, G6, H6,... cells, would a good idea? so, it shows like:

Do Range("Database", row#=$F$2, iColumn=i6).Value = Range ("$i$6")
until i<= n

where 'i' is a variable and 'n' is an amount of subjects

i have attached a new code. the workbook is with txt ext since I cant appload a file with xls extension. password is the same - password

Hi,

this version is better than the previous version, although I don't need the password (just CTRL/Break at the inputbox window and 'End').

To attach files (in XP), use the Explore (My Computer) and Archive the file to a .zip, it can then be uploaded.

Is a loop needed? - or can you just wait until the user selectes a cell in the F6:F17 range, the Target.address (or Activecell.address) will indicate which grade is being selected.

For the 'left side' mentioned, if you have variable subjects you may be best to Match the subject for this row to the List of Subjects that you are updating. In VB that's set a variable to WorksheetFunction.Match(Range("A1"), Sheets("Sheet2").Range("$B$1:$B$999"), 0) etc, and use that as an offset to the cell you are updating.

added, Range("A1").select will work.

Let me know how it progresses.
---

[gsheets]

I am getting an error mess saying that the right side not suppose 2b a constant. Please see attached image of the screenshot.

the line I am using is:

Application.WorksheetFunction.Match(Range("$E$2"), Sheets("Sheet1").Range("$B$20:$C$49"), 0) = Range("$F$6")

I also tried the same withput "Application.", the same result.

Is it possible to take step by step & get only 1 cell changed - instead of changing an entire range is it possible to change just 1 cell corresponding to the date?

basically, i want cell (in 'Database' range) correponding to cell $E$2 to be overwritten with value of cell $F$6

[Bryan Hessey]

I am getting an error mess saying that the right side not suppose 2b a constant. Please see attached image of the screenshot.

the line I am using is:

Application.WorksheetFunction.Match(Range("$E$2"), Sheets("Sheet1").Range("$B$20:$C$49"), 0) = Range("$F$6")

I also tried the same withput "Application.", the same result.

Is it possible to take step by step & get only 1 cell changed - instead of changing an entire range is it possible to change just 1 cell corresponding to the date?

basically, i want cell (in 'Database' range) correponding to cell $E$2 to be overwritten with value of cell $F$6

yeah, that's certainly stuffed (technical term meaning 'not working as desired')

The Match, in Excel or VB looks for an item in a range and returns a relative row number for the item within the range.

You need to say

mo = WorksheetFunction.Match(Range("E2"), Sheets("Sheet2").Range("B20:B49"), 0)

hth
---

[gsheets]

I think I was over analyzing things & thus making thing more complex than they really are (= Not "dont break if aint broken"). Here is what I came to after reviewing your notes & reading online abit about formula variables, object & property

Sub Test1()
x = Application.Match(Range("$E$2"), Worksheets("Sheet1").Range("A20:A49"), 0)
MsgBox x
Cells(19 + x, 3).Value = Range("$F$6")

End Sub

It is working PERFECT!
Love You!

[Bryan Hessey]

I think I was over analyzing things & thus making thing more complex than they really are (= Not "dont break if aint broken"). Here is what I came to after reviewing your notes & reading online abit about formula variables, object & property

Sub Test1()
x = Application.Match(Range("$E$2"), Worksheets("Sheet1").Range("A20:A49"), 0)
MsgBox x
Cells(19 + x, 3).Value = Range("$F$6")

End Sub

It is working PERFECT!

good to see it's working for you, and thanks for the response.

---