Posts by ginobuonpanino

I don't understand. The select case is target.address. Not a string. So how I have to declare the select case statement?

Hi, I want to use select case with a named range. the code I'm using is:

Sub Worksheet_SelectionChange(ByVal Target As range)


Dim i As Integer
Dim m As Integer
Dim n As Integer


Select Case Target.Address


    'Case "$A$1"
        'MsgBox "stop"
        'Exit Sub
'not used by now


    Case Is = range("named_range1")
        MsgBox "it works"
        m = Selection.Row
        n = Selection.Column
        For n = n + 1 To n + 4
            With Cells(m, n)
                .Interior.Color = RGB(255, 255, 0)
                .Value = True
                .Font.Color = RGB(255, 255, 0)
            End With
            With Cells(m + 1, n)
                .Interior.Color = RGB(255, 255, 0)
                .Value = True
                .Font.Color = RGB(255, 255, 0)
            End With
        Next n
    Case Is = range("named_range2")
       msgbox "it works2"
        m = Selection.Row
        n = Selection.Column
        For n = n + 1 To n + 4
            With Cells(m, n)
                .Interior.Color = RGB(146, 208, 80)
                .Value = False
                .Font.Color = RGB(146, 208, 80)
            End With
            With Cells(m - 1, n)
                .Interior.Color = RGB(146, 208, 80)
                .Value = False
                .Font.Color = RGB(146, 208, 80)
            End With
        Next n


    'Case "$H$2"
        'Set ws = Worksheets("Sheet1")
        'Call VBAProject.Module1.test(ws)
'not used by now, but if the code works I will use it


End Select
End Sub

Now nothing happened, I prefer to not use the if form as usual. The ranges are composed of cells every three rows and every 7 columns, like A1 A8 A4 A8...

thanks for you help!

Files are attached to first post. sorry...:thanx:

First file BOOKING

I select the seats I want to book using the checkboxes, then, I write the name inside the textbox and I press book.
The program loop throught the line in names sheets and if there is a true value it write the booking's name in the next cell. This file is very slow in old PCs.

Secondo file testtest

Here I select the seats I want so book by double click. the value of the seats turn from false to true. Then i write the name od the person ho wantis to book and I select the yellow cell to book. To write the reservation It use the same loop of the old file.

Hi, I'm trying to improve the performance of a spreadsheet I've made that has to work in old pcs. The use of this spreadsheet is as a tables reservation. You can check the seat you want to reservate and then he write the name in the list. The spreadsheet works great but I'm trying to rewrite the code to avoid the use of objects (I think that this is one of the things that slow down my macros). Now I have 3 questions.

1. Is it true that a lot of object could slow down my excel file (I have more or less 300 checkboxes, and a couple of buttons)?
2. If I use the event macros (BeforeRightClick, BeforedoubleClick and SelectionChange), instead of objects, could I improve the performance of this file in old pcs?
3. Once I select the seats in sheet1, I linked the cell from this sheet to sheet2 to give true or false value based on the seat selected (If selected true, empty false). I create a macro using the for function (from bigining of the column to last value), that with a if funcion find the true values and write the name in a cell in sheet1 to the next cell in sheet2. Is there a better way to do this? Avoiding the use of loops? Maybe with a vlook?

Thanks for you reply!

Re: Add new button in row of an excel table and assign a macro

Ok, it works great now, but you make me think. Is there a way to show the edit button just in the selected row next to where I click? It's just an idea, maybe it's impossible, but I want to ask. Anyway Thank a lot!

Re: Add new button in row of an excel table and assign a macro

that's the file I'm working with... It's in italian I hope it's not a problem, in case I will traslate it... There is only 2 rowas by now. As you can see the button don't fit perfectly and it appears only when I select the row, and finaly just for the selected row.

https://www.dropbox.com/s/elm70ngkv9tzun7/Sample.xlsm?dl=0

Thanks for the help

Re: Add new button in row of an excel table and assign a macro

Thanks for the help cytop... I've tryed and it works, but not as I wish. The button don't fit completely the cell (it's a little on the left) and moreover it's not always visible. I'm not an expert one in vba, but there is not a way to just use the .offset (rowcount, 18) to put the button in this cell (everytime I add a new row to the excel table? Or to make it always visible?

Hi, I have an excel table with 16 columns. I want to add another one and in the 17th I want to put a button named "edit" and assign to it the macro "macro1". To write in the excel table I use this code:

RowCount = Worksheets("Soci").Range("A1").CurrentRegion.Rows.Count
    
    With Worksheets("Soci").Range("A1")
        .Offset(RowCount, 0).Value = Me.lblMatricola.Caption
        .Offset(RowCount, 1).Value = Me.txtNome.Value
        .Offset(RowCount, 2).Value = Me.txtCognome.Value
    Select Case True
        Case Me.optFemmina
        .Offset(RowCount, 3).Value = "F"
        Case Me.optMaschio
        .Offset(RowCount, 3).Value = "M"
    End Select
        .Offset(RowCount, 4).Value = Me.txtAnnoIscrizione.Value
    If Me.chkScheda.Value = True Then
        .Offset(RowCount, 5).Value = "Si"
    Else
        .Offset(RowCount, 5).Value = "No"
    End If
        .Offset(RowCount, 6).Value = Me.cboTipoSocio.Value
    If Me.chkCarri.Value = True Then
        .Offset(RowCount, 7).Value = "Si"
    Else
        .Offset(RowCount, 7).Value = "No"
    End If
        .Offset(RowCount, 8).Value = Me.txtIndirizzo.Value
        .Offset(RowCount, 9).Value = Me.txtPaese.Value
        .Offset(RowCount, 10).Value = DateValue(Me.txtDataNascita.Value)
        .Offset(RowCount, 11).Value = Me.txtPaeseNascita.Value
    If Me.txtCodiceFiscale1.Value = "" Then
        .Offset(RowCount, 12).Value = Me.txtCodiceFiscale2.Value
    Else
        .Offset(RowCount, 12).Value = Me.txtCodiceFiscale1.Value
    End If
        .Offset(RowCount, 13).Value = Me.txtCellulare.Value
        .Offset(RowCount, 14).Value = Me.txtEmail.Value
      End With

So every time I write a new row to the excel table I add even a button. Is there a way?

Thanks

Re: Age in Userform automatic update

It works, but not as in your example. When I add something new it creates a new row with all the data, but, it also add another row with just the formulas in the last 2 cells. So it add 2 rows everytime.

Quote from royUK;752575

Covert the data to a Table format

Overview of Tables

Change the code in the UserForm to this

Code

Option Explicit
Private Sub CommandButton1_Click()
    Dim oTbl As ListObject
    Dim ONewRw As ListRow
    Dim oCtl As Control
    Dim iX As Integer




    ' Scrittura campi in excel
    ' Reset campi
    Set oTbl = Sheet1.ListObjects(1)
    Set ONewRw = oTbl.ListRows.Add(AlwaysInsert:=True)


    iX = 1


    For Each oCtl In Me.Controls
        If TypeName(oCtl) = "TextBox" Then
            ONewRw.Range.Cells(1, iX).Value = oCtl.Value
            oCtl.Value = ""
            iX = iX + 1
        End If
    Next oCtl


End Sub

Display More

Display More

Re: Age in Userform automatic update

Up

Re: Age in Userform automatic update

here

forum.ozgrid.com/index.php?attachment/66219/

Hi, I have a table with different entries, I fill this table using a Userform, so I don't have a fixed number of rows; at the end of the table I put 2 columns:

the first calculate the age based to the date of birth (FORMULA =DATEDIF(K2;TODAY();"Y") )

the seconf column say if the guy is adult or not (FORMULA =IF(P2>=16;"adult";"young") )

I extend the formula to the whole column, but...

the first of this 2 columns is filled with 115 and the second with adult. Because I need to say how many adult and young there is in my table (and at the moment I have every cell with adult I'm trying to extend this formulas only after I fill a new row.

I have an idea, if I add to my code to write the formulas in the cell? Or if you have any ideas they are welkome

RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    
    With Worksheets("Sheet1").Range("A1")
        .Offset(RowCount, 0).Value = Me.lblA.Caption
        .Offset(RowCount, 1).Value = Me.txtB.Value
        .Offset(RowCount, 2).Value = Me.txtC.Value
    Select Case True
        Case Me.optF
        .Offset(RowCount, 3).Value = "F"
        Case Me.optM
        .Offset(RowCount, 3).Value = "M"
    End Select
        .Offset(RowCount, 4).Value = Me.txtD.Value
    If Me.chkS.Value = True Then
        .Offset(RowCount, 5).Value = "yes"
    Else
        .Offset(RowCount, 5).Value = "no"
    End If
        .Offset(RowCount, 6).Value = Me.cboT.Value
    If Me.chkCAR.Value = True Then
        .Offset(RowCount, 7).Value = "yes"
    Else
        .Offset(RowCount, 7).Value = "no"
    End If
        .Offset(RowCount, 8).Value = Me.txtI.Value
        .Offset(RowCount, 9).Value = Me.txtQ.Value
        .Offset(RowCount, 10).Value = DateValue(Me.txtDOB.Value)
        .Offset(RowCount, 11).Value = Me.txtCOB.Value
    If Me.txtCF1.Value = "" Then
        .Offset(RowCount, 12).Value = Me.txtCF2.Value
    Else
        .Offset(RowCount, 12).Value = Me.txtCF1.Value
    End If
        .Offset(RowCount, 13).Value = Me.txtPH.Value
        .Offset(RowCount, 14).Value = Me.txtEM.Value


HERE I THINK TO ADD THE FORMULAS (IN COLUMNS 15 AND 16)




      End With

Thanks for your help

Re: Copy range to particular cell repetitively

Now works great! Thanks

Re: Copy range to particular cell repetitively

mmm... interesting... I think that the best way to save paper is to use just a single page with just a single ticket... I can find smaller paper size to fit the output layout. So the macro must populate the first ticket, print, clean the cells and loop... or create empty tickets and then print many times as are the rows with contents in sheet1. I attach a sample file...

Re: Copy range to particular cell repetitively

I'm sorry, I'm working with D4n13l, but now he is on holiday, thats why I'm posting. The workbook I attached is right. I don't put the code, because, I made some changes, because D4n13l used merged cell that caused some error so I simplified the layout to avoid those merged cells.

Re: Copy range to particular cell repetitively

It works but not as I hope. I attach a workbook as example... (I simplified the layout in sheet 3 I hope that help you)

forum.ozgrid.com/index.php?attachment/66148/

Thanks for your precius help

Re: Copy range to particular cell repetitively

It works but not as I hope. I attach a workbook as example... (I simplified the layout in sheet 3 I hope that help you)

forum.ozgrid.com/index.php?attachment/66148/

Re: Activate active x textform pressing enter

My idea was to write what I want to put in sheet1 (in activated cells) and without pressing the button to activate the macro activate the macro by just pressing enter. So I don't need to use the mouse, I only have to write the name and push enter.

Hi, I create this simple code. I have a column in Sheet1 filled with bolean operator (true/false, activated by checkboxes). The code below search for true value and if the next cell is empty it fills it with the content
written in the active x textbox I put in Sheet2 (where there are the checkboxes) and finally it cleans the text form. In sheet2 I even put a button to which I assigned this macro (that I write in Sheet1 objects). My question is, there is a way to activate the textbox by pressing enter (Key 13 if I'm not mistaken).

Public Sub BookingInt()    Dim indexCol As Integer
    Dim indexRow As Integer
    
    indexCol = 3
    indexRow = 5
    
   
    Do While indexRow < 1220
       If Cells(indexRow, indexCol + 1).Value = "True" And Cells(indexRow, indexCol).Value = "" Then
               Cells(indexRow, indexCol).Value = Worksheets("Sheet2").OLEObjects("TextBox1").Object.Text
       End If
       indexRow = indexRow + 1
    Loop
    
    Worksheets("Sheet2").OLEObjects("TextBox1").Object.Text = ""
    
End Sub

Thanks for any help!