Posts by X10A

Hi,

I have an add-in with an attached customised toolbar. If I add a new button to the toolbar, it will be available. However, if I unload the add-in and reload again, every changes to the toolbar buttons and menu items will be lost.

This is an example of what I have done:
1) I add a button to the add-in toolbar.
2) In VB editor, I change the IsAddin property to False so that the addin workbook is visible.
3) Under the Tools->Customized, I attached the toolbar to the add-in workbook.
4) In VB editor, I change the IsAddin property back to True.
5) After I unload and reload the add-in again, the new button is not available on the toolbar.

Can anyone tell me where have I go wrong? Thanks.

Re: Can you write a macro to delete the first few words of a cell?

Hi,

There is a way around this but it involves using MS Word.
1) In Excel, select that one cell and press Ctrl+C
2) Open Word, and press Ctrl+V
3) Then from the Menu, go to Table, Convert, Table to Text, use Paragraph Marks and press OK.
4) Now each email entries will be separated by paragraph marks.
5) Press Ctrl+A, followed by Ctrl+C
6) Go back to Excel and paste
7) Now the email entries are separated by rows.
Apply pangolin's formula for each cell and you should have what you want.

Re: X-axis to show years

Hi Bill767,

Try this;

1) Right-click on your chart and select Source Data from the menu
2) In the Category (X) Axis labels, Type in "=Sheet1!$A$3:$A$8" (exclude the quotes)
3) Click OK.
4) Right-click on the chart again and choose chart options.
5) Go to the Axes tab.
6) Under the Primary Axis, Category (X) axis, choose the option button "category" instead of "Time-scale".
7) Click ok.

If everything went well, you should see the X-axis is now goes from 2000 to 2005.

Re: Calendar Output

Hi, RoyUK
The excel VBA code in the zip file under post #23 was protected. Is it ok to post a unprotected one? as i would like to see what is the VBA code behind the scene. Tks.

Re: Calendar Variant

Hi Fengore,

Thanks for getting back. The revised calendar still cannot work on my system. However, when I changed some lines of your code, everything works fine. The changes are as follows:

In the UserForm_Initialize Event:

d = CDate(Day(Now()) & "/" & Month(Now()) & "/" & Year(Now()))

In the DetermineCalVariables procedure:

FirstDayOfMonth = CDate("1" & "/" & Month(CurrentTargetDate) & "/" & Year(CurrentTargetDate))


c = CDate("1" & "/" & Month(FirstDayOfMonth + 45) & "/" & Year(FirstDayOfMonth + 45))

It seems that when I use date sequence [Day, Month, Year] instead of [Month, Day, Year], the code will work. Although I could not understand why I have to change the date sequence in order to get it working, but that's what I have to do to make it work.

I hope my solution will help others with the same problem.

Thanks again, Fengore, for your great calendar. It rocks!!

Re: Calendar Variant

Hi Fengore,

I have use your workbook on 8 May 2006. However, when I opened the calendar, it shows August 5, 2006. I believe this may have something to do with my regional settings as I use the format "dd mmm yyyy". I like your calendar control, but how can I go about the day and month issue?

I have been using comments quite a lot and sometimes I just want to have a comment for all selected cells, and sometimes different comments for each cell in selection. Therefore, I have the following code that does what I want.

In here, I would like to point out that most of the code below have been referred from others' vba postings. I have only modified the code for this thread's purposes, so I won't dare to take credit for anything.

Last of all, I apologise if the code below is not up to standard. Thanks.

'Code start here


Sub CommentsInSel()


On Error GoTo 0


Dim CurRng As Range, NewRng As String, DelExistComments As Integer
Dim OneByOne As Integer, DefComments As String


Application.ScreenUpdating = True


'define the selection range
Set CurRng = Selection


'check if existing comments should be deleted
For Each myCell In CurRng
    myCell.Select
    
    If myCell.Comment Is Nothing Then
        If NewRng = "" Then
        NewRng = myCell.Address
        Else
        NewRng = myCell.Address & "," & NewRng
        End If
    ElseIf Not myCell.Comment.Text = "" Then 'if there is a comment
        myCell.Comment.Visible = True
DelExistComments = MsgBox("Cell " & myCell.Address & " has existing comments, do you want to delete it?", _
vbYesNo, "Comments in cell is not empty")
        If DelExistComments = vbYes Then
            myCell.ClearComments
            If NewRng = "" Then
            NewRng = myCell.Address
            Else
            NewRng = myCell.Address & "," & NewRng
            End If
        ElseIf DelExistComments = vbNo Then
            myCell.Comment.Visible = False
        End If
    End If
Next myCell




Range(NewRng).Select


'choose whether selected range received same comments
OneByOne = MsgBox("Enter comments One By One for each cell?", vbYesNoCancel, "Comments")


If OneByOne = vbCancel Then
    Exit Sub
ElseIf OneByOne = vbYes Then
    For Each myCell In Range(NewRng)
        myCell.Select
        myCell.AddComment
        DefComments = InputBox("Pls key in comments for cell " & myCell.Address)
        myCell.Comment.Text Text:=DefComments
    Next myCell
    Range(NewRng).Select
Else
    DefComments = InputBox("Pls key in the single comments for ALL the selected cells.")
    For Each myCell In Range(NewRng)
        myCell.Select
        myCell.AddComment
        myCell.Comment.Text Text:=DefComments
    Next myCell
    Range(NewRng).Select
End If


Set CurRng = Nothing
NewRng = "'"
DelExistComments = Empty
OneByOne = Empty
DefComments = ""




End Sub

'End of Code