UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error When Having Allot Of Coment.text, Office 2013    
 
   
Miguel_A
post Feb 14 2018, 02:54 AM
Post#1



Posts: 231
Joined: 20-January 04



Hi

I have added a bunch of if's statements to my work calendar, however and because of the use of allot Cells(etc).Comment.Text Text:="text here" it's giving the error "Object variable or With block variable not set" ...
When using the Comment.Text i am not using any Variable why is it giving the error ?

Below is the entire code part, if i take it script works fine
CODE
            If DateSerial(a_year, a_month, a_day) = "01-01-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text "Este Ano Novo"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "30-03-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Sexta-Feira Santa"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "01-04-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Páscoa"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "25-04-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Dia da Liberdade - 25 de Abril"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "01-05-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Dia do Trabalhador"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "31-05-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Corpo de Deus"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "10-06-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Dia de Portugal"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "15-08-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Assunção de Nossa Senhora"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "05-10-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Implantação da República"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "01-11-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Dia de Todos os Santos"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "01-12-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Restauração da Independência"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "08-12-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Dia da Imaculada Conceição"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            ElseIf DateSerial(a_year, a_month, a_day) = "25-12-2018" Then
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).Comment.Text Text:="Natal"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            End If


Thanks in advance
Go to the top of the page
 
cheekybuddha
post Feb 14 2018, 04:45 AM
Post#2


UtterAccess VIP
Posts: 10,261
Joined: 6-December 03
From: Telegraph Hill


You left out the ':=' between comment.text and Este ano novo in the first assignment.

Also, beware you DateSerial comparison condition, it may yield unexpected results as you will be comparing a date to a string.

hth,

d

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


Regards,

David Marten
Go to the top of the page
 
HairyBob
post Feb 14 2018, 04:56 AM
Post#3



Posts: 991
Joined: 26-March 08
From: London, UK


Hi Miguel,

Try:

CODE
Cells(p_ln + 1, p_col + 1).NoteText "Text in Comment"


See: https://msdn.microsoft.com/en-us/VBA/excel-...xt-method-excel
This post has been edited by HairyBob: Feb 14 2018, 04:58 AM
Go to the top of the page
 
cheekybuddha
post Feb 14 2018, 05:27 AM
Post#4


UtterAccess VIP
Posts: 10,261
Joined: 6-December 03
From: Telegraph Hill


Ah yes, thanks HairyBob! thumbup.gif

I was trying to read this code on my phone, and it wasn't easy!

I didn't realise that Comment.Text() was a method and not a property

It seems as if Comment.Text() and Range.NoteText() work the same way.

Miguel - you get error "Object variable or With block variable not set" because the cell does not have a comment object until you add one.

So, either use Range.NoteText() or change your code to:
CODE
' ...
               Cells(p_ln + 1, p_col + 1).AddComment.Text "Este Ano Novo"
' ...


Regarding your date comparisons, since you already have variables for month, day and year, you might be better off using:
CODE
            Select Case True
            Case a_year = 2018 AND a_month = 1 AND a_day = 1
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).AddComment.Text "Este Ano Novo"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
            Case a_year = 2018 AND a_month = 3 AND a_day = 30
                Cells(p_ln + 1, p_col + 1).Interior.ColorIndex = 6
                Cells(p_ln + 1, p_col + 1).AddComment.Text Text:="Sexta-Feira Santa"
                Cells(p_ln + 6, p_col + 1).Interior.ColorIndex = 6
' ...
            End Select



hth,

d

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


Regards,

David Marten
Go to the top of the page
 
HairyBob
post Feb 14 2018, 06:54 AM
Post#5



Posts: 991
Joined: 26-March 08
From: London, UK


No probs David thumbup.gif

The only thing I would say, is that if the code is run more than once on a qualifying day, with AddComment, you will get an 'Application defined or Object defined error' as the Comment object will already exist. In order to avoid the error, you would need to delete the existing Comment object first:

CODE
...
If Not Cells(p_ln + 1, p_col + 1).Comment Is Nothing Then Cells(p_ln + 1, p_col + 1).Comment.Delete
Cells(p_ln + 1, p_col + 1).AddComment.Text "Este Ano Novo"
...


Hairy.
This post has been edited by HairyBob: Feb 14 2018, 06:56 AM
Go to the top of the page
 
cheekybuddha
post Feb 14 2018, 06:58 AM
Post#6


UtterAccess VIP
Posts: 10,261
Joined: 6-December 03
From: Telegraph Hill


Top catch!

Range.NoteText() is the way to go!

thumbup.gif

d

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


Regards,

David Marten
Go to the top of the page
 
Miguel_A
post Feb 21 2018, 05:38 PM
Post#7



Posts: 231
Joined: 20-January 04



Hi

First of all want to say sorry about my delay in the reply ...
I saw the reply in the second day after i ask, however i was not able (due to the lack of time) to check the code and if all was working (after the solution given to me).

Now it's all working fine indeed and thanks for the help and for all the discussion about it ...

Thanks in advance
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st August 2018 - 06:11 PM