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.

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?

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?

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:

Sub auto_open()
   Load UserForm1
   UserForm1.Show
   Sheet27.Select
End Sub




Private Sub UserForm_Initialize()
 With Application
        .WindowState = xlMinimized 
        .Height = 1
        .Width = 1
 End With
    ComboBox1.Clear
    ComboBox2.Clear
::
::
End Sub

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.

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.

If Not ActiveCell.Comment Is Nothing Then
                        Exit Sub
                    Else
                    resp5 = MsgBox("Enter comments?!  Proceed? (Y/N)", vbYesNo + vbDefaultButton2, "Comments")
                    If resp5 = 7 Then
                        Exit Sub
                    ElseIf resp5 = 6 Then
                        Me.Unprotect Password:="xxxl"    ', UserInterfaceOnly:=True
                        Target.Locked = False
                        Target.AddComment
                        ActiveCell.Comment.Visible = True
                        addCom1="Enter comments here>"
                        addCom2=ActiveCell.Comment.text
                        ActiveCell.Comment.Text Text:=addCom1 & AddCom2
                        ActiveCell.Value = "Y" 
                        ActiveCell.Comment.Text Text:=AddCom2
                    End If
                    End If

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.

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:

.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

Thanks, Dave. Then I stop pondering how to get other workaround.

Cheers.

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?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
:
:
resp5 = MsgBox("Enter comments?!  Proceed? (Y/N)", vbYesNo + vbDefaultButton2, "Comments")
                    If resp5 = 7 Then
                        Exit Sub
                    ElseIf resp5 = 6 Then
                        Me.Unprotect Password:="xxxl"                        Target.Locked = False
                        Target.AddComment
                        ActiveCell.Comment.Visible = True
                        ActiveCell.Comment.Text Text:=""
                        ActiveCell.Value = "Y"
                    End If
:
:

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:

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.

Hello there. I use "ActiveWindow.ScrollWorkbookTabs Position:=xlFirst" to display all available sheets in a workbook. But can the worksheet of the first tab (i.e. at the far left) be automatically selected after the workbook is opened?

Cheers.

Re: Disable Read-only Message Box

Hi Batman. Thanks. I'm afraid I got myself misunderstood. I want to have the ready-only / notify message box (see attached) disappear when the already opened file was retrieved by other user.

Can I do that?

Cheers.