Posts by newvbie

    Hi there. I got the following code to reset the interior colorindex of cells in several sheets before the worksheets closed. But the myrngstring, I think, was not allowed to be the pre-set range. I got an error message.


    Code
    For k = 5 To ofname 
       If Trim(Cells(k, 2).Value) = ckname Then 
          myrngstring = Range(Cells(k, 1), Cells(k, 34)).Address 
          For Each ws In Worksheets 
             If ws.Visible Then 
                ws.Range(myrngstring).Interior.ColorIndex = xlNone 
             End If 
          Next 
       End If 
    Next


    Can anyone please advise?


    Cheers.

    Re: Copy TextBox Formats & Text


    Hi Dave. The created 2 textboxes, namely textbox1 and textbox2. Of textbox1, the property was set to : font: bold & underlined. When I typed text in textbox1, the text appeared to be bold and underlined. I clicked on the commandbutton1, text was copied to textbox2 without having the font bold and underlined. I tried the same code to copy the text from textbox1 and use CTRL-V to paste the text (being bold and underlined textbox1) to the ms notepad and only normal text was copied thereon. Tha's the result.


    Any further advice please.


    Cheers.

    Re: Copy TextBox Formats & Text


    Hi there. Thanks for your reply. So, if there is a sentence "this is a test" of which the font style is bold, font size is 10 in Times Roman and underlined, can all the font features of textbox1 be copied to, say, the notepad when I use the combined key CTRL-V?


    Code
    Private Sub copytext_Click() 'press to copy the text (with underline and bold) of textbox1
    
    
    Dim MyDataObj As New DataObject 
    MyDataObj.SetText "" 
    MyDataObj.PutInClipboard 
    MyDataObj.SetText TextBox1.Text 
    MyDataObj.PutInClipboard 
    End Sub


    Thank you in advance.


    Cheers.

    Hi there. Can the PutInClipboard expression copy the font size and the added underline feature of the object (i.e. textbox3.text) as well?

    Code
    Dim MyDataObj As New DataObject
    MyDataObj.SetText ""
    MyDataObj.PutInClipboard
    MyDataObj.SetText TextBox3.Text
    MyDataObj.PutInClipboard



    Cheers.

    Re: Userform Fails To Pop Up


    Hi there. Thanks for your reply. I've indeed tried to place the code in the workbook_open module but the form did not pop up unless user click on the excel icon on the status bar.


    Any advice please.


    Cheers.

    Hi there. I have a userform for user to log in before start of the application. The userform was intentionally to top up of the application and I have made the worksheet minimized when the userform was loaded. However, when the form was loaded in auto_run module, it always stayed in the status bar and users had to click on the excel icon to pop up the userform. Here is my code:



    Can anyone please advise?


    Cheers.

    Re: Disable Read-only Message Box


    Hi,there. Thanks for you reply. I've tried again but the message still pops up. I think Dave is right that the code would NOT fire until after the message shows.


    I've put the code in the workbook_open module.. but the message keeps popping up.


    Code
    Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    If Workbooks.Open("c:\test.xls").ReadOnly Then
        ActiveWorkbook.Close SaveChanges:=False
    End If
    Application.ScreenUpdating = True
    End Sub


    Thanks anyway.


    Cheers.

    Re: Set Focus To Cell Comment Box


    Hi there. I've tried the following code but it seems something wrong with that.



    Hope anyone can help.


    Cheers.

    Re: Set Focus To Cell Comment Box


    Hi there. I tried the following code but failed to do the job.


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    With Target
    .comment.Replace _
        What:="Type comment here:", Replacement:="", _
        SearchOrder:=xlByColumns, MatchCase:=True


    How to rectify the above, please?


    Cheers.

    Re: Set Focus To Cell Comment Box


    Hi Dave. Thanks. I've tried found that although the cursor did not appear in the comment box, when I typed anything, that "anything" could appear in the comment box. I think it works for me in that way. By the way, if I add "Type comment here>" in the comment.text by vba as follows:



    Code
    .comment.text text:="Type comment here>"


    After user enters "I'll take leave on dd/mm/yyyy" in the comment box, can I use vba to remove "Type comment here>" after user enters the comment?


    Thank you.

    Re: Disable Read-only Message Box


    Sorry. I just can't skip by vba the read-only, notify and cancel message box when the file I open is already opened by other. Can it actually be skipped?


    Cheers.

    Re: Set Focus To Cell Comment Box


    Hi there. Thanks Dave. I tried your codes but I got the same result...the cursor cannot focus in the comment box. User may erroneously enter the remarks into the cell instead of the comment box.


    Any other alternatives?


    Cheers.

    Hi there. I use vba to allow user to add comments in a cell. Can I make the comment box be setfocused so that user will not enter comments into the cell instead?




    Further, can the cell be assigned a "Y" and locked after user responds "Yes" and the comment box is then setfocus?


    Cheers.

    Re: Generate A Monthly Job Chart


    Hi there. Thanks for your response. I attached here an excel file which I tried this morning. The C flag appears on Sundays thought I known you have set the condition to skip assignment on Sundays.


    Thanks.

    Re: Generate A Monthly Job Chart


    Hi Batman. Thanks for your codes. It works when I first created a command button to run your codes. But when I pressed the button the second time, the codes seemed to loop endlessly and what I was waiting was a hourglass on the screen. I think I must miss something there. Only 1 line of your codes has been modified:


    Code
    intDays = Range("IV3").End(xlToLeft).Column 
    where range("IV1") changed to range("IV3") because the days start on row 3.


    Any more advice please.


    Cheers.

    Re: Auto Selection Of Worksheet Of First Tab


    Hi there. I just want to have the worksheet of the first tab (ie. far left) selected when the workbook is opened. Is there any expression something like "xlfirsttab" to achieve what I want? The name of the sheet is not known because it depends on what the user prefer to name.


    Cheers.

    Re: Auto Selection Of Worksheet Of First Tab


    Hello there. I got about 20 sheets and some of them are hidden (by vba for security reasons) and some are visible. Then the sheet number on the first tab may not be the sheet1. When I add a new sheet(by vba), it may be hidden or visible. That's why I use "ActiveWindow.ScrollWorkbookTabs Position:=xlFirst" to display all visible sheets. But what if I want to have the sheet of first tab (the sheet number is unknown) selected?


    Cheers.