Omar
12/19/2006 6:55:00 PM
Dear Mr. Peterson,
I appreciate your help, it works just fine.
I didn't know that when a button is defined in a worksheet, all the button's
click events are related to that worksheet.
Thank you.
Happy holidays,
Omar.
"Dave Peterson" wrote:
> This looks like it could be the _click event for a commandbutton placed on a
> worksheet.
>
> If that's the case, then any unqualified ranges refer to the worksheet that owns
> the code--not the activesheet.
>
> So when you do this kind of stuff:
>
> Worksheets(2).Select
> Select Case valor
> .....
> Case 8
> celda = "H" & renglon
> Range(celda).Select
> Range(celda).Value = 1
> valor = CInt(Range(celda).Value)
>
> range(celda) will refer to the worksheet with the button (and code). It won't
> refer to worksheets(2)--the now active worksheet.
>
> And selecting this range won't work, since worksheets(2) is now active. (You
> can only select a cell on an activesheet).
>
> You could do this:
>
> Worksheets(2).Select
> Select Case valor
> .....
> Case 8
> celda = "H" & renglon
> worksheets(2).Range(celda).Select
> worksheets(2).Range(celda).Value = 1
> valor = CInt(worksheets(2).Range(celda).Value)
>
> but even better would be to drop the .selects
>
> 'don't do this: Worksheets(2).Select
> Select Case valor
> .....
> Case 8
> celda = "H" & renglon
> with worksheets(2)
> 'no need to do this: Range(celda).Select
> .Range(celda).Value = 1
> valor = CInt(.Range(celda).Value)
> ....
>
> Note the dots in front of the range objects. That means that that
> object/property belongs to the object in the previous "with" statement--in this
> case, that's Worksheets(2).
>
>
>
> Omar wrote:
> >
> > Hi Mr. Keramidas,
> >
> > The whole (complete?) code is:
> >
> > Private Sub btnNext_Click()
> > '''''''''''''''''''''''''''''''''''
> > ' Declaraci�³n de variables
> > '''''''''''''''''''''''''''''''''''
> > Dim valor, renglon, valorCelda As Integer
> > Dim vacio As Boolean
> > Dim celda As String
> > '''''''''''''''''''''''''''''''''''
> > ' Asignaci�³n de valores
> > '''''''''''''''''''''''''''''''''''
> > renglon = 3
> > vacio = False
> > celda = "IF" & renglon
> > '''''''''''''''''''''''''''''''''''
> > ' Determinar rengl�³n disponible
> > '''''''''''''''''''''''''''''''''''
> > Worksheets(2).Select
> > Do While vacio = False
> > 'Range(celda).Select
> > valorCelda = CInt(Range(celda).Value)
> > If valorCelda = 0 Then
> > vacio = True
> > Else
> > renglon = renglon + 1
> > End If
> > Loop
> > '''''''''''''''''''''''''''''''''''
> > ' 1.1
> > '''''''''''''''''''''''''''''''''''
> > Worksheets(1).Select
> > valor = CInt(Range("E16").Value)
> > Worksheets(2).Select
> > Select Case valor
> > Case 1
> > celda = "A" & renglon
> > Range(celda).Value = 1
> > ...
> >
> > "renglon" in spanish means row, so I declared this variable to find an empty
> > row where the data must be placed. It's initiated with value 3 because the 2
> > firsts rows have some information.
> >
> > Than you.
> >
> > "Gary Keramidas" wrote:
> >
> > > i don't see anywhere where renglon is defined, it is empty. it needs to be a
> > > row number
> > >
> > >
> > > --
> > >
> > >
> > > Gary
> > >
> > >
> > > "Omar" <Omar@discussions.microsoft.com> wrote in message
> > > news:D2A678DD-0F6A-4472-85D5-FF1BA701B153@microsoft.com...
> > > > Hi,
> > > >
> > > > I've got the next code:
> > > >
> > > > Worksheets(1).Select
> > > > valor = CInt(Range("E16").Value)
> > > > Worksheets(2).Select
> > > > Select Case valor
> > > > Case 1
> > > > celda = "A" & renglon
> > > > Range(celda).Value = 1
> > > > Case 2
> > > > celda = "B" & renglon
> > > > Range(celda).Value = 1
> > > > Case 3
> > > > celda = "C" & renglon
> > > > Range(celda).Value = 1
> > > > Case 4
> > > > celda = "D" & renglon
> > > > Range(celda).Value = 1
> > > > Case 5
> > > > celda = "E" & renglon
> > > > Range(celda).Value = 1
> > > > Case 6
> > > > celda = "F" & renglon
> > > > Range(celda).Value = 1
> > > > Case 7
> > > > celda = "G" & renglon
> > > > Range(celda).Value = 1
> > > > Case 8
> > > > celda = "H" & renglon
> > > > Range(celda).Select
> > > > Range(celda).Value = 1
> > > > valor = CInt(Range(celda).Value)
> > > > Case 9
> > > > celda = "I" & renglon
> > > > Range(celda).Value = 1
> > > > Case 10
> > > > celda = "J" & renglon
> > > > Range(celda).Value = 1
> > > > End Select
> > > >
> > > > I've been executing it with "valor" as 8; however, it always returns the
> > > > 1004 error: an application error. (Amazing, isn't it?!!!)
> > > >
> > > > By the way, it's supposed that "Range(celda).Value = 1" writes, stores,
> > > > -whatever- "1" in the cell, right? Well, guess what? It doesn't work. Which
> > > > is the correct sentence to do that?
> > > >
> > > > Thanks in advance.
> > > >
> > > > Omar.
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>