Posts by Ruddles

Re: Buttons Get Bigger When Clicked

Quote from justforgroups;603159

The exact same thing has just started happening on one of my user's Excel. Everyone else is fine! Any ideas?

What happened when you tried my suggestion?

Re: Auto-increment number in text box

Xposted at http://www.mrexcel.com/forum/showthread.php?t=493289

Re: Break out multi-line cell text into individual cells

Please feel free to try this:-

Option Explicit
 
Public Sub Splitter()
 
  Dim iPtr As Integer
  Dim iBreak As Integer
  Dim strTemp As String
  Dim iRow As Integer
 
  iRow = 0
  For iPtr = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    strTemp = Cells(iPtr, 1)
    iBreak = InStr(strTemp, vbLf)
    Do Until iBreak = 0
      If Len(Trim(Left(strTemp, iBreak - 1))) > 0 Then
        iRow = iRow + 1
        Cells(iRow, 2) = Left(strTemp, iBreak - 1)
      End If
      strTemp = Mid(strTemp, iBreak + 1)
      iBreak = InStr(strTemp, vbLf)
    Loop
    If Len(Trim(strTemp)) > 0 Then
      iRow = iRow + 1
      Cells(iRow, 2) = strTemp
    End If
  Next iPtr
 
End Sub

Re: Help required using milliseconds

The final separator - between seconds and milliseconds - should be a dot (full stop, period), not a colon.

Re: Expiry Date Message Pop-Up

Xposted: http://www.mrexcel.com/forum/showthread.php?t=492844

Re: timestamp in a cell and lock it with password

Create a hidden worksheet and keep the timestamps there. Make your main worksheet refer to those. Make the timestamps worksheet xlveryHidden so only VBA can access it and protect VBA with a password. When the workbook is opened, go through the cells in your main worksheet makiing sure the timestamp fields still point to the real timestamps in the hidden worksheet.

Does that sound like a possible approach?

Re: How to convert text to rows in excel?

What's actually in that first cell, character by character - do you know? What does =LEN(A1) give you?

Re: Grab google results amount from webpage import to excel sheet

Something along these lines will get you going:-

Option Explicit
 
Public Sub GetResults(argString As String)
 
  Dim objShell As Object
  Dim objIE As Object
 
  Const cDivId As String = "<div id=resultstats>"
  Const cTerm As String = "<nobr>"
  Dim iPtr As Long
  Dim sResults As String
 
  Set objShell = CreateObject("Wscript.Shell")
  Set objIE = CreateObject("InternetExplorer.Application")
  With objIE
    .Visible = False
    .Silent = True
    .Navigate ("[URL]http://www.google.co.uk/search?q[/URL]=" & argString)
    Do Until .ReadyState = 4
      DoEvents
    Loop
    With .Document.Body
      iPtr = InStr(LCase(InnerHTML), cDivId)
      If iPtr = 0 Then
        ' do {something} when string not found
        MsgBox "Argh!" ' or whatever!
        Exit Sub
      End If
      sResults = Mid(LCase(.InnerHTML), iPtr + Len(cDivId))
      iPtr = InStr(LCase(sResults), cTerm)
      sResults = Left(sResults, iPtr - 1)
      ' now you have "about x,xxx results" - use the string as it is or slice it up further
      Range("A1") = "Google reports: " & sResults
    End With
  End With
 
  objIE.Quit
  Set objIE = Nothing
  Set objShell = Nothing
 
End Sub

I'll leave it to your ingenuity to slice the results string up further if you just want the number. Alternatively, you could consider making this into a function which returns the results as a Long value. Shout if you don't know how to do this.

Re: Multiple Lists in a single cell

May I humbly submit my own first attempt?

I've never played around with this sort of thing before so please feel free to tell me it's rubbish!

Re: Copy active cell value to specific cell using click event

Cancel = True

You have no idea how much time I've spent trying to achieve that! Now please excuse me whilst I go and edit a dozen or so worksheets...

Re: How to put a value in a cell by clicking a specified range fo cells

I'm reminded of...
http://www.bowdoin.edu/~disrae…ustomer-really-needed.jpg

Re: excel 2007 if then else statemen

I'm assuming you want a formula to put in your worksheet and not VBA code...

Put this in B1: =IF(A1=1,F1,"")

Re: How to put a value in a cell by clicking a specified range fo cells

This will do what you want if you're happy to double-click the cell:-

Option Explicit
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
  If Not Intersect(Target, Range("C2:C6")) Is Nothing Then
    Range("A1") = Target.Value
  End If
 
End Sub

It needs to go in the module for the sheet: hit Alt-F11, double-click the sheet name in the VBA project and paste the code in.

Any good?

Re: Macro to protect all sheets in a workbook but allow users to format cells

Have you tried using the macro recorder and looking at the code it generates?

Re: Please Help...Urgent Attention

What message? It works fine here.

Re: Need help resolving a type mismatch

Try:-

call sub2 (wrdDoc)

or:

sub2 wrdDoc

Re: Sorting data fields by rank

Rats, - beat me to it! :smile: Stick this in F33:-

=IF(OR(E33=E32,E33=E34),"=","")

and copy down.

Re: Sorting data fields by rank

Sort the entire table using the column your RANK is in as the sort field. You have My data has headers unchecked, yes?

Re: Calculation based on changing conditions

D'oh! <slaps forehead>

Re: Calculation based on changing conditions

Set C1 to:-

=A1*B1

Set C2 to:-

=IF(ISBLANK(B2),A2*C1/A1,A2*B2)

Then copy C2 down.

Any good?