Posts by azzaeff

Re: Disable macro for keying in data in range of cells

Hello Mr Carim,

Your code works very well. I can feel that keying in data is a bit faster now.

Thank you very much!

Hello guys,

I have created a table with list of scholar's ID and name in column A and B respectively, name of class subjects which the scholar are taking in column E to K, Pre-test columns for all subjects in column L to CL and Post-test columns for all subjects in column CM to FM. I have used this VBA code which will only filter the scholar names and subject taken based on the dropdown (created using data validation) selected in cell B2, and hide any other rows/columns. This code actually works perfectly for this purpose:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:=""
    Sheet1.Hide_Columns_Containing_Value
    ActiveSheet.Range("$D$6").AutoFilter Field:=4, Criteria1:=Range("$B$2").Value
    ActiveSheet.Protect Password:=""
    Application.ScreenUpdating = True
End Sub


Sub Hide_Columns_Containing_Value()
Dim c As Range
    For Each c In Range("E3:FM3").Cells
        If c.Value = 0 Then
            c.EntireColumn.Hidden = True
        End If
        If c.Value = 1 Then
            c.EntireColumn.Hidden = False
        End If
    Next c
End Sub

However, every time my data entry staff keying in data in the pre/post test columns (column L to FM), there's a slight delay after pressing ENTER or TAB to the next cell. Keying in several data will result in noticeable sluggishness. I understand that this is most likely due to the nature of the Worksheet_Change function which will run whenever there's any change in any cell. I've read and find out that to temporarily disable this macro, we can use the Application.EnableEvents function. But unfortunately I do not really know to use this function, so I would like to seek your kind help on this.

In short, I would like to disable macro whatsover whenever any data is keyed in in the pre/post test columns which will help reduce the sluggishness. Only just enable just for the cell B2 selection dropdown changes, which will reveal/hide columns/rows based on that selection. For your information, the whole range of the pre/post test columns is L7:FM132.

Thank you for any kind help.

-Shahiran

Re: Adding a char to another number entries with several 'alt enter' space in a cell

Hello MrRedli,

Thanks for the reply. I've tried using your answer, but it does not produce the output I desire. It becomes like this:

12377377237777853277452

I believe this is because that there are multiple 'alt-enter' space inbetween the numbers, so it substitute them as well. What I would like to do is to add a single character right next to the number, while still mantaining the same gap (the same 'alt-enter' space). Thanks for the suggestion anyway.

If there's anyone else can suggest any solution, I would be very grateful.

Thanks!

Hello sir!

I think this is my very 1st post in this forum. Previously I was just a 'lurker', reading and getting solutions to most of my Excel problems, thanks to all of you! But now, here is one problem which I could not find it either here or on any Google search links. Here it goes:

Supposed that I have a cell, say A1, which contains some numbers with multiple 'alt enter' space in it, like this:
123
73

23

8532

452

Notice that the gap between the numbers varies between one another, depending on how many 'alt enter' space is used. My aim is to add a single character or number just immediately after the current keyed number while still mantaining the gap between them. So for the first example, say i would like to add a letter 'a' next to each of the numbers, the expected output would be:
123a
73a

23a

8532a

452a

or, if I would add number '7' next it each of it, it will be like this:
1237
737

237

85327

4527

Is it really possible to do so? It doesn't matter if the formula will be very long and complex. I prefer the Non-VBA way but if using VBA is much easier, I can still consider it.

Thank you so much for any help.