Tuesday, September 26, 2006

Excel and VBA - Locking a Cell

Its been a long time I even saw any code in VB let alone VBA. That was until somebody asked me how they could lock a particular cell in excel based on the list selection in another cell.

That as usual sent me googling and finally I managed to write a script that will do the job for me. For writing the code, I opened the excel sheet and pressed Alt + F11 to open the Macro VBA Editor and selected the worksheet for which I wanted to write the functionality for and then wrote this piece of code on the worksheet change event. I wanted to lock cells C1 to C10 based on the value of cell D1.

Private Sub Worksheet_Change(ByVal Target As Range)
If (Intersect(Target, Range("D1")) Is Nothing) Or _
                    (Target.Cells.Count > 1) Or _
                    Not (Target.Column = 4 And Target.Row = 1) Then Exit Sub
         If Range("D1").Value = "V1" Then
                  ActiveSheet.Unprotect
                  Range("A1:IV65536").Locked = False
                  Range("C1:C10").Locked = True
                  ActiveSheet.Protect
         Else
                  ActiveSheet.Unprotect
                  Range("A1:IV65536").Locked = False
         End If
End Sub

Then my colleague wanted something different. He said, that he wanted the user to be alerted that value cannot be entered into a cell based on the selected value of another cell for which I modified the above code slightly to get this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Target.Column = 6 Or _
           Target.Column = 9 Or _
            Target.Column = 12) Then Exit Sub
    If Not (ActiveSheet.Cells(Target.Row, Target.Column - 1).Value = "C")  _
     And Not (ActiveSheet.Cells(Target.Row, Target.Column).Value = "")       
     Then
             MsgBox ("No value possible here")
             ActiveSheet.Cells(Target.Row, Target.Column).Value = ""
    End If
End Sub

The above code alerts the user if the user tries to enter some value in a cell when it makes no sense to do so because of the selected value of another column.

Tuesday, September 19, 2006

Uninstalling Linux without screwing up Windows

I have a Windows XP, Mandrake Linux dual boot PC. I had installed Linux after windows as normally anybody will do.  I had done this almost 2 years back and unfortunately out of nowhere I had given a 2 GB Partition for the Windows Installation Drive. I know, I know...What on earth was I thinking?

My system started complaining of too less space in the OS directory and applications started behaving abnormally (probably because they expected some space in the temp directory and didnt find much). So I decided to uninstall Linux and resize by Windows Installation Partition. The problem was I didnt have a clue how to?

So I went on a search on how to uninstall Linux without screwing up the windows installation because simply deleteing the partition will screw up my MBR and would leave my hard disk useless.

Finally I did finf a way. I used the Windows Installation CD to boot and went inside the Windows Recovery Console (Press F10 or R). I then used the command fixmbr to overwrite my MBR.

Then I restarted the machine which automatically booted to my XP and then used the Windows Device Manager to delete and format the Linux Partitions.

I then used Partition Magic to resize my OS installed Drive and then restarted my machine to apply changes. It all went like a charm!